Transform data into 2 more columns

Question

Post reply

Transform data into 2 more columns

SkyBVI

SSCoach

Points: 15128
More actions
July 20, 2012 at 2:15 am

#276552

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,
Skybvi
DBA
Regards
Sushant Kumar
MCTS,MCP

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

July 20, 2012 at 2:22 am

#1515351

Is this homework? What have you tried so far?

John

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

Look up Ranking functions here is an example

create table #t1 (id int,col varchar(5))

insert into #t1 select 102,'#1' union all

select 102,'#2' union all

select 102,'#3' union all

select 105,'#4' union all

select 105,'#5' union all

select 107,'#6'

select *,row_number() over (partition by id order by col) as RN,

(select count(*) from #t1 a where a.id=b.id group by id)

from #t1 b

***The first step is always the hardest *******

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

This will also work:

create table #t1 (Invoice_no int, item VARCHAR(5), price MONEY)

insert into #t1 select 102,'#1', 6.21

union all select 102,'#2', 3.56

union all select 102,'#3', 4.28

union all select 105,'#4', 1.90

union all select 105,'#5', 3.66

union all select 107,'#6', 2.01

SELECT Invoice_no, item, price

,invoice_line=COUNT(Invoice_no) OVER (PARTITION BY Invoice_no)

,invoice_line#=ROW_NUMBER() OVER (PARTITION BY Invoice_no ORDER BY (SELECT NULL))

FROM #T1

DROP TABLE #T1

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

SkyBVI SSCoach Points: 15128 More actions · Answer 4

ya i got it now...

thanks all of you for helping me out..

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP