data manipulation - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

data manipulation

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-09-20 : 05:24:44

I have used this forum before with some great results.

I have a table #source where I have a number of Accounts with a seqID. I want to unpack the seqID so as all the values in the c* fields sit on a single row.
Is there a way of scripting with T-sql to make the data appear like it does in table #desiredoutput?

Any advice would be great
Thanks

CREATE TABLE #source

(
Account nvarchar(10) NULL,
SeqID int NULL,
c1 nvarchar(4) NULL,
c2 nvarchar(4) NULL,
c3 nvarchar(4) NULL,
c4 nvarchar(4) NULL,
c5 nvarchar(4) NULL
)

Insert into #source
select 'cu001', 1 ,'U073' , 'Y981' , NULL , NULL , NULL UNION all
select 'cu001', 2 ,'U073' , 'Y981' , 'Y981' , NULL , NULL UNION all
select 'cu001', 3 ,'U077' , 'Y221' , 'Y941' , NULL , NULL UNION all
select 'cu002', 1 ,'U071' , NULL , NULL , NULL , NULL UNION all
select 'cu002', 2 ,'U071' , 'Y221' , NULL , NULL , NULL

CREATE TABLE #desiredoutput
(
Account nvarchar(10) NULL,
c1 nvarchar(4) NULL,
c2 nvarchar(4) NULL,
c3 nvarchar(4) NULL,
c4 nvarchar(4) NULL,
c5 nvarchar(4) NULL,
c6 nvarchar(4) NULL,
c7 nvarchar(4) NULL,
c8 nvarchar(4) NULL
)
Insert into #desiredoutput
select 'cu001', 'U073' , 'Y981' ,'U073' , 'Y981' , 'Y981','U077' , 'Y221' , 'Y941' UNION all
select 'cu002', 'U071' , 'U071' , 'Y221' , NULL , NULL, NULL , NULL , NULL

select * from #source
select * from #desiredoutput

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-09-21 : 06:12:58

Hi all,

I haven’t had any replies to this post

. Is there anything I can do to improve the post? or is anything unclear?

I have had great help in the past & any today would be brill.

Many thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-21 : 06:49:52

Sorry - imho that's not a "data manipulation" problem.
It is a presentation problem and it should be done in the front end.

Too old to Rock'n'Roll too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 06:58:34

Here is one way, which I am not too happy about - if you have a front end system such as reporting services or a client application, best to do it there.

SELECT
	Account,
	MAX(CASE WHEN RN = 1 THEN Acc END) AS c1,
	MAX(CASE WHEN RN = 2 THEN Acc END) AS c2,
	MAX(CASE WHEN RN = 3 THEN Acc END) AS c3,
	MAX(CASE WHEN RN = 4 THEN Acc END) AS c4,
	MAX(CASE WHEN RN = 5 THEN Acc END) AS c5,
	MAX(CASE WHEN RN = 6 THEN Acc END) AS c6,
	MAX(CASE WHEN RN = 7 THEN Acc END) AS c7,
	MAX(CASE WHEN RN = 8 THEN Acc END) AS c8
FROM
(
	SELECT *,
	       ROW_NUMBER() OVER(PARTITION BY Account ORDER BY Account, SeqID, r) AS 
	       RN
	FROM   #source
	       UNPIVOT(Acc FOR r IN (c1, c2, c3, c4, c5)) U
) s GROUP BY Account

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-21 : 07:04:14

quote:

Originally posted by sunitabeck

Here is one way, which I am not too happy about - if you have a front end system such as reporting services or a client application, best to do it there.

SELECT
	Account,
	MAX(CASE WHEN RN = 1 THEN Acc END) AS c1,
	MAX(CASE WHEN RN = 2 THEN Acc END) AS c2,
	MAX(CASE WHEN RN = 3 THEN Acc END) AS c3,
	MAX(CASE WHEN RN = 4 THEN Acc END) AS c4,
	MAX(CASE WHEN RN = 5 THEN Acc END) AS c5,
	MAX(CASE WHEN RN = 6 THEN Acc END) AS c6,
	MAX(CASE WHEN RN = 7 THEN Acc END) AS c7,
	MAX(CASE WHEN RN = 8 THEN Acc END) AS c8
FROM
(
	SELECT *,
	       ROW_NUMBER() OVER(PARTITION BY Account ORDER BY Account, SeqID, r) AS 
	       RN
	FROM   #source
	       UNPIVOT(Acc FOR r IN (c1, c2, c3, c4, c5)) U
) s GROUP BY Account

So here the story ends with c8, Hmm...

Too old to Rock'n'Roll too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:06:48

quote:
Originally posted by webfred

So here the story ends with c8, Hmm...

Too old to Rock'n'Roll too young to die.

What could one possibly want to do with more than 8?

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-09-21 : 07:15:41

Thank you so much for your help.

I can see how you have looked to do this, i had been trying UNPIVOT without success.

When i run the T-sql below i return an error:
Incorrect syntax near the keyword 'FOR'.
I’m using sql 2005 sp3 compatibility level 2000 (80)
would this be linked to the error?
CREATE TABLE #source
(
Account nvarchar(10) NULL,
SeqID int NULL,
c1 nvarchar(4) NULL,
c2 nvarchar(4) NULL,
c3 nvarchar(4) NULL,
c4 nvarchar(4) NULL,
c5 nvarchar(4) NULL
)

Insert into #source
select 'cu001', 1 ,'U073' , 'Y981' , NULL , NULL , NULL UNION all
select 'cu001', 2 ,'U073' , 'Y981' , 'Y981' , NULL , NULL UNION all
select 'cu001', 3 ,'U077' , 'Y221' , 'Y941' , NULL , NULL UNION all
select 'cu002', 1 ,'U071' , NULL , NULL , NULL , NULL UNION all
select 'cu002', 2 ,'U071' , 'Y221' , NULL , NULL , NULL

SELECT
Account,
MAX(CASE WHEN RN = 1 THEN Acc END) AS c1,
MAX(CASE WHEN RN = 2 THEN Acc END) AS c2,
MAX(CASE WHEN RN = 3 THEN Acc END) AS c3,
MAX(CASE WHEN RN = 4 THEN Acc END) AS c4,
MAX(CASE WHEN RN = 5 THEN Acc END) AS c5,
MAX(CASE WHEN RN = 6 THEN Acc END) AS c6,
MAX(CASE WHEN RN = 7 THEN Acc END) AS c7,
MAX(CASE WHEN RN = 8 THEN Acc END) AS c8
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Account ORDER BY Account, SeqID, r) AS
RN
FROM #source
UNPIVOT(Acc FOR r IN (c1, c2, c3, c4, c5)) U
) s GROUP BY Account

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-21 : 07:16:36

quote:
Originally posted by sunitabeck

quote:
Originally posted by webfred

So here the story ends with c8, Hmm...

Too old to Rock'n'Roll too young to die.
What could one possibly want to do with more than 8?

The answer to life the universe and everything = 42

Too old to Rock'n'Roll too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:32:47

quote:
When i run the T-sql below i return an error:
Incorrect syntax near the keyword 'FOR'.
I’m using sql 2005 sp3 compatibility level 2000 (80)

UNPIVOT will work only on 2005 or later and compatibility level 90 or greater.

You can do a manual unpivot using UNION ALL.

SELECT account, SeqID, c1 FROM #source WHERE c1 IS NOT NULL UNION ALL 
SELECT account, SeqID, c2 FROM #source WHERE c2 IS NOT NULL UNION ALL 
SELECT account, SeqID, c3 FROM #source WHERE c3 IS NOT NULL UNION ALL 
SELECT account, SeqID, c4 FROM #source WHERE c4 IS NOT NULL UNION ALL 
SELECT account, SeqID, c5 FROM #source WHERE c5 IS NOT NULL

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-09-21 : 08:21:42

Genius! Thanks sunitabeck.
Tried & works perfectly in sql 2005 (90).
Another great piece of help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 10:06:31

Glad to help and glad you got it figured out :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources