Road Block - I am completely stumped

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)

Road Block - I am completely stumped

Author

Topic

Echooo3
Starting Member

2 Posts

Posted - 2013-12-11 : 10:42:27

How can I get the table on top to display like the table on bottom? Let's call the table "CustomerInfo".

ID NextID Purch# Sequence Type NameFull
22xx22 657xxxxxx056 70xx057 2 R Jane Doe
22xx22 657xxxxxx056 70xx057 3 P John Doe
33xx33 576xxxxxx092 12xx8543 1 R Danny Doe
33xx33 576xxxxxx092 12xx8543 2 P Mary Doe
33xx33 576xxxxxx092 12xx8543 3 A Molly Doe
99xx99 62xxxxxx655 8xx2583 2 P Richard Doe
99xx99 62xxxxxx655 8xx2583 3 R Cynthia Doe

------------------------------------------------------------------

ID NextID Purch# Sequence Type NameFull Sequence Type NameFull Sequence Type NameFull
22xx22 657xxxxxx056 70xx057 2 R Jane Doe 3 P John Doe
33xx33 576xxxxxx092 12xx8543 1 R Danny Doe 2 P Mary Doe 3 A Molly Doe
99xx99 62xxxxxx655 8xx2583 2 P Richard Doe 3 R Cynthia Doe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 12:30:13

something like


SELECT ID,NextID,[Purch#],
MAX(CASE WHEN Rn =1 THEN Sequence END) AS Sequence1,
MAX(CASE WHEN Rn =1 THEN Type END) AS Type1,
MAX(CASE WHEN Rn =1 THEN NameFull END) AS NameFull1,
MAX(CASE WHEN Rn =2 THEN Sequence END) AS Sequence2,
MAX(CASE WHEN Rn =2 THEN Type END) AS Type2,
MAX(CASE WHEN Rn =2 THEN NameFull END) AS NameFull2,
MAX(CASE WHEN Rn =3 THEN Sequence END) AS Sequence3,
MAX(CASE WHEN Rn =3 THEN Type END) AS Type3,
MAX(CASE WHEN Rn =3 THEN NameFull END) AS NameFull3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID,NextID,[Purch#] ORDER BY Sequence) AS Rn,*
FROM table
)t
GROUP BY ID,NextID,[Purch#]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 12:33:50

for making it dynamic use

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Echooo3
Starting Member

2 Posts

Posted - 2013-12-11 : 12:57:55

Thank you visakh16 that solved the problem!!! There have been 5 people in this office trying to get this to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources