Transform data into 2 more columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transform data into 2 more columns

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-07-20 : 04:09:12

Hi,
I have data in a table wid 3 columns.
Invoice_no, item, price....

example:-

Invoice_no item price
102 my item #1 $6.21
102 my item #2 $3.56
102 my item #3 $4.28
105 my item #4 $1.90
105 my item #5 $3.66
107 my item #6 $2.01

I wanted to convert this data into something like this :--( adding 2 mroe columns)

Invoice_no item price invoice_line invoice_line#
102 my item #1 $6.21 3 1
102 my item #2 $3.56 3 2
102 my item #3 $4.28 3 3
105 my item #4 $1.90 2 1
105 my item #5 $3.66 2 2
107 my item #6 $2.01 1 1

Let me know how to add those 2 columns

Regards,
Sushant
DBA
Virgin Islands(U.K)

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-20 : 04:59:32

ALTER TABLE YourTableName ADD [invoice_line] INT, [invoice_line#] INT

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-07-20 : 05:05:13

quote:
Originally posted by sql-programmers

ALTER TABLE YourTableName ADD [invoice_line] INT, [invoice_line#] INT

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Ok...But how to populate those 2 columns with the data as intended.?

Regards,
Sushant
DBA
Virgin Islands(U.K)

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-20 : 05:25:44

You need create update query for each row using primary key.

May i know , Did you stored that values in any table or have any logic to update that values in that column?

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-07-20 : 05:46:18

hi

assuming that invoiceLine is number of row per invoice and invoiceLine# is current row in invoice

here is

with A
as
(
select 102 as Invoice_no, 'my item #1' as item, $6.21 as price
union all
select 102,'my item #2', $3.56
union all
select 102, 'my item #3', $4.28
union all
select 105,'my item #4', $1.90
union all
select 105 ,'my item #5', $3.66
union all
select 107 ,'my item #6', $2.01

)
select A.*,B.invoiceLine ,
row_number() over (partition by A.invoice_no order by A.item asc ) as invoice_line#

from A
inner join
(select count (invoice_no)as invoiceLine , invoice_no from A group by invoice_no)B on A.invoice_no=B.Invoice_no

S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources