Query using PIVOT - 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)

Query using PIVOT

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2007-12-27 : 02:09:05

[code]I’ve a table with 2 columns with the following data

Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007

I would like to get the output in the following format using PIVOT function

2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish

Help me to write the query…

--
ash
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 03:04:16

Is the order of listing of Col1 values important? If not, you can use this:-

;
WITH Col_CTE (RowNo,Col1,Col2) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Col2 ORDER BY NEWID()) AS 'RowNo',
Col1,
Col2
FROM Table
)

SELECT t1.Col1,t2.Col1,t3.Col1
FROM Col_CTE t1
INNER JOIN (SELECT RowNo,Col1 FROM Col_CTE WHERE Col2=2006) t2
ON t2.RowNo=t1.RowNo
INNER JOIN (SELECT RowNo,Col1 FROM Col_CTE WHERE Col2=2007)t3
ON t3.RowNo=t1.RowNo
WHERE t1.Col2=2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-27 : 21:35:00

The cte + pivot way

DECLARE @sample TABLE
(
	Col1	varchar(20),
	Col2	int
)
INSERT INTO @sample
SELECT	'Vista',		2005 UNION ALL
SELECT	'Distinguish',		2005 UNION ALL
SELECT	'ColonialVoice',	2006 UNION ALL
SELECT	'Vista',		2006 UNION ALL
SELECT	'Vista',		2007 UNION ALL
SELECT	'SuperiorCard',		2007 UNION ALL
SELECT	'Distinguish',		2007

;WITH sample_data (RowNo, Col1, Col2)
AS
(
	SELECT	RowNo = row_number() OVER (PARTITION BY Col2 ORDER BY Col1),
		Col1,
		Col2
	FROM	@sample
)
SELECT	[2005], [2006], [2007]
FROM	sample_data
pivot
(
	MAX(Col1)
	FOR Col2 IN ([2005], [2006], [2007])
) p

/*	RESULT :
2005                 2006                 2007                 
-------------------- -------------------- -------------------- 
Distinguish          ColonialVoice        Distinguish
Vista                Vista                SuperiorCard
NULL                 NULL                 Vista

(3 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

sqlilliterate
Starting Member

40 Posts

Posted - 2007-12-27 : 22:17:00

Thnx for the solution KH,

Also ur ans. can be simplified as below...

SELECT [2005],[2006],[2007] FROM (SELECT col1,col2,row_number() OVER (PARTITION BY col2 ORDER BY col2) Col3 FROM @sample) P
PIVOT (MAX(Col1) FOR Col2 in ([2005], [2006], [2007])) AS PVT

Thanks again :)

--
ash

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources