December 16, 2008 at 10:15 pm
Hi,
i need help on this
i have a table called tableA
WHICH HAS VALUES AS FOLLOWS
COLUMN A COLUMN B
7464350012645.35
7464350014190.94
What i need INSTEAD is to get results as follows in a new table B as follows
COLUMN A COLUMN B COLUMN C
7464350012645.35 4190.94
December 16, 2008 at 10:33 pm
This could be as easy as a simple pivot or a more complex dynamic pivot. Based on your data provided, it is a little difficult to know for sure. Are all entries like the sample, 2 rows for a given "account id" or could there be entries that contain more than 2 rows that need to be pivoted to a single row?
December 16, 2008 at 10:36 pm
All entries will be the same as sample
December 16, 2008 at 10:39 pm
Just to be sure, as this is posted in a SQL Server 2005 forum (we have had SQL Server 2000 posts here), that you are using SQL Server 2005. I should have asked in my previous post.
December 16, 2008 at 10:45 pm
iam using 2005.can you help me on this?
December 16, 2008 at 10:50 pm
You will have to test the following code as I am currently applying SP3 to my system here at home.
;with MyData (
rownum,
colA,
colB
) as (
select
row_number() over (partition by colA order by colA),
colA,
colB
from
tableA
)
insert into tableB (columnA, columnB, columnC)
select
colA,
max(case when rownum = 1 then colB else null end),
max(case when rownum = 2 then colB else null end)
from
MyData
group by
colA;
Let me know if this works.
December 17, 2008 at 12:53 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply