Copy current record (again)

Hi there,
What I have is an appointments diary, what I want is to be able to move
details from one record to another (when someone calls to move their
appointment from one day & time to another).
Tablename: AppDetails includes the following fields:
Appdate smalldatetime (PK)
Apptime varchar(!) 10 (PK) - this not one of my tables!
Appname varchar 100
Address1....Address3 all varchar 100
Postcode varchar 20
The table is filled with 24 times (9.00am, 9.30am...8.30pm) per date over
ten years!
The user sees one record to view and what I'd like is an sp to copy all the
values in the visible record (not Appdate or Apptime) into @.Variables that
could be called from a button and another to paste them onto the new record
(the user will select the new record).
Where I'm stuck is how to select the Current record.
Help please
Thanks
Paul> Where I'm stuck is how to select the Current record.
You select a row by it's *Key*. As far as SQL is concerned there is no
such thing as a "current record" - it is your client app's job to pass
the key back to SQL Server.
How does this question differ from the one you asked last w?
http://www.google.co.uk/groups?selm...r />
roups.com
You're likely to get more help if you follow the suggestion I made then
about posting full details.
David Portas
SQL Server MVP
--|||Sounds like an ideal question for Joe to reply ;)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Paul in Harrow" <PaulinHarrow@.discussions.microsoft.com> wrote in message
news:7BF7D25A-2BBE-4942-8921-2A6E4FFF83E0@.microsoft.com...
> Hi there,
> What I have is an appointments diary, what I want is to be able to move
> details from one record to another (when someone calls to move their
> appointment from one day & time to another).
> Tablename: AppDetails includes the following fields:
> Appdate smalldatetime (PK)
> Apptime varchar(!) 10 (PK) - this not one of my tables!
> Appname varchar 100
> Address1....Address3 all varchar 100
> Postcode varchar 20
> The table is filled with 24 times (9.00am, 9.30am...8.30pm) per date over
> ten years!
> The user sees one record to view and what I'd like is an sp to copy all
> the
> values in the visible record (not Appdate or Apptime) into @.Variables that
> could be called from a button and another to paste them onto the new
> record
> (the user will select the new record).
> Where I'm stuck is how to select the Current record.
> Help please
> Thanks
> Paul
>|||"David Portas" wrote:

> You select a row by it's *Key*. As far as SQL is concerned there is no
> such thing as a "current record" - it is your client app's job to pass
> the key back to SQL Server.
> How does this question differ from the one you asked last w?
It's the same question but I've finally got some some time to work on it.
Thanks
Paul|||Then, just to re-iterate: SQL knows nothing about what row is "current"
within your application. I imagine the logical sequence will be:
1. User chooses an appointment time.
2. User selects "copy". Application saves data for the row or just the
key values for the row.
3. User chooses a new appointment time.
4. User selects "paste". Application updates the table based on the key
values of the new row (@.ad2 and @.at2 in my original example) and the
data saved in Step 2.
Step 4 is the only SQL data modification operation.
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> Then, just to re-iterate: SQL knows nothing about what row is "current"
> within your application. I imagine the logical sequence will be:
> 1. User chooses an appointment time.
> 2. User selects "copy". Application saves data for the row or just the
> key values for the row.
> 3. User chooses a new appointment time.
> 4. User selects "paste". Application updates the table based on the key
> values of the new row (@.ad2 and @.at2 in my original example) and the
> data saved in Step 2.
Spot on
I've been trying to avoid doing any of this with the front end app as it's
sooo slow.

> Step 4 is the only SQL data modification operation.
Many thanks for your help
Paul

Copy current record

Hi There
Tablename "tblAppDetails"
Which includes the following fields
AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
varchar 100, AppAddress1 varchar 100
What I'd like are 2 SP's, the first to select the all the fields (other than
AppDate & AppTime) for the current record and the second to paste those
values onto another record in the same table (this is for moving someone fro
m
one appointment to another. The user sees one record to view and'll click a
button to copy, select a new record and click another button to paste) .
Is this something to do with Fetch?
Many thanks
PaulPaul in Harrow wrote:
> Hi There
> Tablename "tblAppDetails"
> Which includes the following fields
> AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
> varchar 100, AppAddress1 varchar 100
> What I'd like are 2 SP's, the first to select the all the fields
> (other than AppDate & AppTime) for the current record and the second
> to paste those values onto another record in the same table (this is
> for moving someone from one appointment to another. The user sees one
> record to view and'll click a button to copy, select a new record and
> click another button to paste) .
> Is this something to do with Fetch?
> Many thanks
> Paul
The PK in the table seems a little iffy. What prevents two records from
having the same date and time?
With that PK, why not just change the date and time to make the change.
I assume by "current record" you mean a row of data that a user is
editing. I'm not sure what you mean by "another record" because of the
PK.
Could you explain in a little more detail what data you want to move,
where it's going and if any other tables are involved in the process.
David Gugick
Imceda Software
www.imceda.com|||You don't need cursors and multiple SPs. A single UPDATE will do it:
UPDATE tblAppDetails
SET appname =
(SELECT appname
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1),
(SELECT appaddress1
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1)
WHERE appdate = @.ad2
AND apptime = @.at2
(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively.
Why are you using separate columns for date and time when SMALLDATETIME
stores both?
David Portas
SQL Server MVP
--|||Thanks both Davids
"David Portas" wrote:
"Why are you using separate columns for date and time when SMALLDATETIME
stores both?"
I know, but this is not my table and it's been pre-filled with 24 AppTimes
for each AppDate for the next five years!
"(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively."
Er, how? @.ad1 &@.at1 are from the current record but how does it know which
is the second record?|||In my query you need to specify the keys for both the source row and
the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
"second row"? You didn't specify how the target row for the change is
to be determined so I assumed you were supplying that information as
parameters in your SP. If you wanted something else then please give us
a full description of the problem: DDL, sample data INSERT statements
and show your required end result. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||David,
I'll do all this on Monday
Paul
"David Portas" wrote:

> In my query you need to specify the keys for both the source row and
> the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
> "second row"? You didn't specify how the target row for the change is
> to be determined so I assumed you were supplying that information as
> parameters in your SP. If you wanted something else then please give us
> a full description of the problem: DDL, sample data INSERT statements
> and show your required end result. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>

copy a record?

Hi,
I need to copy a record in a table that has an identity column. Plus,
I want to copy this record without using a column list in the SQL
statement.
So I searched the net, came up with an elegant solution:
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
however, when tried that I received:
Error 8101 An explicit value for the identity column in table can only
be specified when a column list is used and IDENTITY_INSERT is ON
So searched again and had the following answers:
"...Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
" and someone added:
"...
You need a column list for your INSERT statement:
INSERT t2 (
[id], [first], [org], [rest], [aux] ) SELECT
[ie], [first], [org], [rest], [aux] FROM t1
..."
BUT - I have to ask:
what if I want to insert without having to use a column list? if
i use a column list, then every time a column is added, modified or
deleted I need to change this store procedure too, which results in
doubling maintenance costs.
any ideas? how can I copy a record without using a named column
list?
Thanks very much for ANY idea,
Lior<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
Generate the SQL (including the column list) yourself at runtime
Liz
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?|||FWIW, anyone who submits SQL, or SPL that has INSERT/UPDATE statements that
do not have column lists, gets their code back, I won't even allow it on QA.
The reason is maintenance. If I add a column to a table, the code will
break, and then I have to dig it out of the system to do a hot-fix.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||WHY do you not want to do a column list' I can think if several reasons
why you SHOULD do one.
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||Thanks to all answers. the reason I DONT want a coloumn list is to
avoid having the code breaks when I add/modify/delete a column. I want
to minimize the explicit names of columns and to have something like
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
See? Here I have only one column name. therefore, less maintainence.
does it make sense? thanks for any ideas
thanks,
Lior
Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> WHY do you not want to do a column list' I can think if several reasons
> why you SHOULD do one.
w
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <liormessin...@.gmail.com> wrote in message
> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > Hi,
> > I need tocopy a recordin a table that has an identity column. Plus,
> > I want to copy this record without using a column list in the SQL
> > statement.
> > So I searched the net, came up with an elegant solution:
> > INSERT INTO <table>
> > SELECT *
> > FROM table WHERE (table.ID = @.objID)
> > however, when tried that I received:
> > Error 8101 An explicit value for the identity column in table can only
> > be specified when a column list is used and IDENTITY_INSERT is ON
> > So searched again and had the following answers:
> > "...Before your SQL Statement:
> > SET IDENTITY_INSERT <tablename> ON
> > " and someone added:
> > "...
> > You need a column list for your INSERT statement:
> > INSERT t2 (
> > [id], [first], [org], [rest], [aux] ) SELECT
> > [ie], [first], [org], [rest], [aux] FROM t1
> > ..."
> > BUT - I have to ask:
> > what if I want to insert without having to use a column list? if
> > i use a column list, then every time a column is added, modified or
> > deleted I need to change this store procedure too, which results in
> > doubling maintenance costs.
> > any ideas? how can Icopy a recordwithout using a named column
> > list?
> > Thanks very much for ANY idea,
> > Lior|||Actually, very interesting - here is a great thread I found about the
subject. My problem is exactly as Chris's
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/e59a1ef42bafeff9/23480e2a7fc6dc7a?lnk=gst&q=copy+a+record&rnum=2#23480e2a7fc6dc7a|||The real root problem here seems to be your developement process!! If you
aren't doing an impact analysis when you alter a table and making necessary
modifications to your code then that needs to be rectified, IMHO. BTW, you
can STILL make things work by simply using NULLable columns and/or defining
defaults for your tables, right?
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1189040443.208198.139120@.o80g2000hse.googlegroups.com...
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
>
>
>
>
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> WHY do you not want to do a column list' I can think if several reasons
>> why you SHOULD do one.
> w
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <liormessin...@.gmail.com> wrote in message
>> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
>> > Hi,
>> > I need tocopy a recordin a table that has an identity column. Plus,
>> > I want to copy this record without using a column list in the SQL
>> > statement.
>> > So I searched the net, came up with an elegant solution:
>> > INSERT INTO <table>
>> > SELECT *
>> > FROM table WHERE (table.ID = @.objID)
>> > however, when tried that I received:
>> > Error 8101 An explicit value for the identity column in table can only
>> > be specified when a column list is used and IDENTITY_INSERT is ON
>> > So searched again and had the following answers:
>> > "...Before your SQL Statement:
>> > SET IDENTITY_INSERT <tablename> ON
>> > " and someone added:
>> > "...
>> > You need a column list for your INSERT statement:
>> > INSERT t2 (
>> > [id], [first], [org], [rest], [aux] ) SELECT
>> > [ie], [first], [org], [rest], [aux] FROM t1
>> > ..."
>> > BUT - I have to ask:
>> > what if I want to insert without having to use a column list? if
>> > i use a column list, then every time a column is added, modified or
>> > deleted I need to change this store procedure too, which results in
>> > doubling maintenance costs.
>> > any ideas? how can Icopy a recordwithout using a named column
>> > list?
>> > Thanks very much for ANY idea,
>> > Lior
>|||On 4 Sep, 23:19, liormessin...@.gmail.com wrote:
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
Please do NOT multi-post. You have several replies in the
microsoft.public.sqlserver.programming newsgroup.
Posting independently to multiple groups wastes everyones time and
makes it harder for people to help you.
--
David Portas|||On Sep 6, 7:00 am, liormessin...@.gmail.com wrote:
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > WHY do you not want to do a column list' I can think if several reasons
> > why you SHOULD do one.
> w
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <liormessin...@.gmail.com> wrote in message
> >news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > > Hi,
> > > I need tocopy a recordin a table that has an identity column. Plus,
> > > I want to copy this record without using a column list in the SQL
> > > statement.
> > > So I searched the net, came up with an elegant solution:
> > > INSERT INTO <table>
> > > SELECT *
> > > FROM table WHERE (table.ID = @.objID)
> > > however, when tried that I received:
> > > Error 8101 An explicit value for the identity column in table can only
> > > be specified when a column list is used and IDENTITY_INSERT is ON
> > > So searched again and had the following answers:
> > > "...Before your SQL Statement:
> > > SET IDENTITY_INSERT <tablename> ON
> > > " and someone added:
> > > "...
> > > You need a column list for your INSERT statement:
> > > INSERT t2 (
> > > [id], [first], [org], [rest], [aux] ) SELECT
> > > [ie], [first], [org], [rest], [aux] FROM t1
> > > ..."
> > > BUT - I have to ask:
> > > what if I want to insert without having to use a column list? if
> > > i use a column list, then every time a column is added, modified or
> > > deleted I need to change this store procedure too, which results in
> > > doubling maintenance costs.
> > > any ideas? how can Icopy a recordwithout using a named column
> > > list?
> > > Thanks very much for ANY idea,
> > > Lior- Hide quoted text -
> - Show quoted text -
When you do not explicitly mention the column it can cause very hard
to find bugs where wrong columns get populated! Tust me on this
because it happened to me. Also, if you add/delete/modify a column,
your code will break when you do not have a column list.

Copy a record from one table to another?

Hello,

Is there a way of copying/moving a record from one table to another identical table on the same database using ADO.NET
I can obviously do it the long way (retrieving a record, then pushing it up using a second SQL command)
I was just wondering if there is a way to do it in one database hit using some kind of cool SQL function.Look at the insert ... select statement in Books on Line

insert into table1
(field1, field2)
select
field1, field2
from table2
where recordid = 1|||If there are no identity fields involved, you can exclude the field names:

INSERT INTO table1
SELECT * FROM table2
WHERE id = 3

Converting records into fields

Hello!
Is there any way in SQL to convert records in fields. Something like
creating a a record with 3 fields from a table with 3 records?
Thanks in advance,
Hugo MadureiraYes and no. What you've asked for is presumably one of two things:
1. Formatting for a report or display. Do that client side. SQL returns
tabular results to the client or middle tier but SQL doesn't control
how those results are formatted. Display of rows, columns, delimitting,
etc is all controlled by your client application. ADO for example
includes the GetString method to do exactly what you've asked.
2. Formatting for export to some external system. Do that in DTS, BCP
or another ETL tool. It is just a question of what delimiter you choose
for columns and rows.
Although yours is quite a common FAQ doing this in SQL has no obvious
advantage and quite a few divantages. We'd need more info to help
you with a specific bit of code for your case: What is the key of your
table? How do you want to define which row transposes to which column
in the result?
Meantime, here's an example:
CREATE TABLE foo (col INTEGER NOT NULL PRIMARY KEY, x INTEGER NOT NULL)
;
INSERT INTO foo (col,x) VALUES (1,777) ;
INSERT INTO foo (col,x) VALUES (2,888) ;
INSERT INTO foo (col,x) VALUES (3,999) ;
SELECT
MIN(CASE WHEN col = 1 THEN x END) AS col1,
MIN(CASE WHEN col = 2 THEN x END) AS col2,
MIN(CASE WHEN col = 3 THEN x END) AS col3
FROM foo ;
David Portas
SQL Server MVP
--|||Hi Hugo
This is usually best to do on the Front End, but if you can distinguish and
link the three records then you can use a self join to do this
e.g.
CREATE TABLE MyAttributes ( id int, type char(1), colour char(10) )
INSERT INTO MyAttributes ( id , type, colour )
SELECT 1, 'A', 'Pink'
UNION ALL SELECT 1, 'B', 'Red'
UNION ALL SELECT 1, 'C', 'Crimson'
UNION ALL SELECT 2, 'A', 'Cyan'
UNION ALL SELECT 2, 'B', 'Blue'
UNION ALL SELECT 2, 'C', 'Navy'
SELECT a.id, a.colour, b.colour, c.colour
FROM MyAttributes a
JOIN MyAttributes b ON a.id= b.id and b.type = 'B'
JOIN MyAttributes c ON a.id= c.id and c.type = 'C'
WHERE a.type = 'A'
John
"Hugo Madureira" wrote:

> Hello!
> Is there any way in SQL to convert records in fields. Something like
> creating a a record with 3 fields from a table with 3 records?
> Thanks in advance,
> Hugo Madureira
>|||Hugo
read this
http://www.aspfaq.com/show.asp?id=2462
--
Regards
R.D
--Knowledge gets doubled when shared
"Hugo Madureira" wrote:

> Hello!
> Is there any way in SQL to convert records in fields. Something like
> creating a a record with 3 fields from a table with 3 records?
> Thanks in advance,
> Hugo Madureira
>|||I'm working on a warehouse management system. My table represents the
products stored in a pallet. I don't know how many products are stored
in the pallet.
I want to copy the name of the products into a new table where the
fields are V01, V02, V02, ...
I've seen examples using case statement but I don't know beforehand the
values in the products name.
David Portas wrote:
> Yes and no. What you've asked for is presumably one of two things:
> 1. Formatting for a report or display. Do that client side. SQL returns
> tabular results to the client or middle tier but SQL doesn't control
> how those results are formatted. Display of rows, columns, delimitting,
> etc is all controlled by your client application. ADO for example
> includes the GetString method to do exactly what you've asked.
> 2. Formatting for export to some external system. Do that in DTS, BCP
> or another ETL tool. It is just a question of what delimiter you choose
> for columns and rows.
> Although yours is quite a common FAQ doing this in SQL has no obvious
> advantage and quite a few divantages. We'd need more info to help
> you with a specific bit of code for your case: What is the key of your
> table? How do you want to define which row transposes to which column
> in the result?
> Meantime, here's an example:
> CREATE TABLE foo (col INTEGER NOT NULL PRIMARY KEY, x INTEGER NOT NULL)
> ;
> INSERT INTO foo (col,x) VALUES (1,777) ;
> INSERT INTO foo (col,x) VALUES (2,888) ;
> INSERT INTO foo (col,x) VALUES (3,999) ;
> SELECT
> MIN(CASE WHEN col = 1 THEN x END) AS col1,
> MIN(CASE WHEN col = 2 THEN x END) AS col2,
> MIN(CASE WHEN col = 3 THEN x END) AS col3
> FROM foo ;
>|||Hugo
There is another article on web. Google for that. With this you can do that
--
Regards
R.D
--Knowledge gets doubled when shared
"Hugo Madureira" wrote:

> I'm working on a warehouse management system. My table represents the
> products stored in a pallet. I don't know how many products are stored
> in the pallet.
> I want to copy the name of the products into a new table where the
> fields are V01, V02, V02, ...
> I've seen examples using case statement but I don't know beforehand the
> values in the products name.
>
> David Portas wrote:
>|||> I want to copy the name of the products into a new table where the
> fields are V01, V02, V02, ...
Why? That sounds like a big design error. Lookup "repeating group" and
First Normal Form if you don't understand why. Product is a single
attribute and should be modelled in a single column.
David Portas
SQL Server MVP
--|||I know exactly what first normal form is. The point here is that my
table with all the products wouldn't be used to store information, just
as a list of products in the pallet to be printed.
David Portas wrote:
>
> Why? That sounds like a big design error. Lookup "repeating group" and
> First Normal Form if you don't understand why. Product is a single
> attribute and should be modelled in a single column.
>|||That was my first assumption too. So I refer you to my original answer:
Reporting isn't a database function.
Of course it's perfectly possible to implement these these things in
the database, but not usually desirable. Try the following or see the
link that R.D. posted.
SELECT
MIN(CASE WHEN col = 1 THEN productname END) AS col1,
MIN(CASE WHEN col = 2 THEN productname END) AS col2,
MIN(CASE WHEN col = 3 THEN productname END) AS col3
/* ... etc */
FROM
(SELECT P1.productname, COUNT(*) AS col
FROM northwind.dbo.products AS P1
JOIN northwind.dbo.products AS P2
ON P1.productname >= P2.productname
GROUP BY P1.productname) AS T ;
David Portas
SQL Server MVP
--