Simple PIVOT question ! (EASY)

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

General SQL Server Forums

New to SQL Server Programming

Simple PIVOT question ! (EASY)

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-10-12 : 14:18:29

Hi all - I'm currently running SQL Server 2008. I have a table that is structured as follows:

REQUEST STEP [DATE]
1234 --- 1 08/01/11
1234 --- 2 08/03/11
1234 --- 3 08/09/11

1235 --- 1 09/01/11
1235 --- 2 09/05/11
1235 --- 3 09/06/11
1235 --- 4 09/14/11

and so forth ...
I'm trying to use the pivot function so that just one request number is outputted for each row, along with the "step" dates:

[REQUEST] [1]-------[2]------[3]------[4]
1234 ---08/01/11 08/03/11 08/09/11
1235 ---09/01/11 09/05/11 09/06/11 09/14/11

What is wrong with my syntax? It outputs multiple request numbers. Any help is greatly appreciated!

SELECT [REQUEST],[1],[2],[3],[5],[6] FROM [TABLE]

PIVOT (MAX ([DATE]) FOR [STEP] IN ([1],[2],[3],[4],[5])) AS
PIVOTTABLE1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:21:14

as per your sample data it should be


SELECT [REQUEST],[1],[2],[3],[4],[5],[6] 
FROM [TABLE]
PIVOT (MAX ([DATE]) FOR [STEP] IN ([1],[2],[3],[4],[5],[6])) AS 
PIVOTTABLE1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-10-12 : 14:31:33

Thanks Visakh, but it just still outputs duplicate request rows, with a repair step date on different lines.
Any other ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:35:36

Nope it wont provided you dont have any other columns with distinct values in your table which you've not shown here

I've used your exact query with same sample data and i got output shown.

see below



SELECT * INTO #Temp
FROM
(
SELECT 1234 AS REQUEST , 1 AS STEP, '08/01/11' AS DATE UNION ALL
SELECT 1234 ,2, '08/03/11'UNION ALL
SELECT 1234 , 3, '08/09/11'UNION ALL

SELECT 1235, 1, '09/01/11'UNION ALL
SELECT 1235 , 2, '09/05/11'UNION ALL
SELECT 1235 ,3, '09/06/11'UNION ALL
SELECT 1235 , 4, '09/14/11'
)t

SELECT *
FROM #Temp
PIVOT(MAX(DAte) FOR STEP IN ([1],[2],[3],[4],[5]))p

DROP TABLE #Temp

output
-------------------------------------------------
REQUEST	1	        2	        3	        4	5
1234	08/01/11	08/03/11	08/09/11	NULL	NULL
1235	09/01/11	09/05/11	09/06/11	09/14/11	NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-10-12 : 15:31:25

Still outputting duplicate rows.
The table has about 20 columns (I had to reduce them to 3 for simplicity).

Any other ideas? THANKS !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 02:01:27

quote:
Originally posted by funk.phenomena

Still outputting duplicate rows.
The table has about 20 columns (I had to reduce them to 3 for simplicity).

Any other ideas? THANKS !!

that explains it. Thats exactly waht i'm telling. If those 20 columns have multiple values per REQUEST value you will surely get multiple rows after pivot.
if you want only these two columns in output exclude others like



SELECT *
FROM (SELECT DISTINCT REQUEST, STEP, [DATE]
 FROM #Temp)t
PIVOT(MAX(DAte) FOR STEP IN ([1],[2],[3],[4],[5]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-10-13 : 10:51:15

Thank you Sir! This worked EXACTLY as I hoped!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 10:57:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources