Doubt in 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)

Doubt in Pivot

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2011-05-18 : 04:47:22

Hello all,

This is my sample data

DECLARE @FEEDBACK TABLE(SECTION VARCHAR(25),SUBSECTION VARCHAR(5))
INSERT INTO @FEEDBACK
SELECT 'PUNCHUALITY','A1' UNION ALL
SELECT 'CONCEPTUAL','A1' UNION ALL
SELECT 'PUNCHUALITY','A2' UNION ALL
SELECT 'CONCEPTUAL','A2' UNION ALL
SELECT 'PUNCHUALITY','A3' UNION ALL
SELECT 'CONCEPTUAL','A3' UNION ALL
SELECT 'PUNCHUALITY','A4' UNION ALL
SELECT 'CONCEPTUAL','A4'

SELECT * FROM @FEEDBACK

My output should be like below
PUNCHUALITY CONCEPTUAL
A1 A1
A2 A2
A3 A3
A4 A4
A5

HOW TO GET LIKE ABOVE, CAN ANY ONE GIVE ME IDEA.

THANKS
GANESH KUMAR V

Solutions are easy. Understanding the problem, now, that's the hard part

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2011-05-18 : 05:51:57

hi all i got an idea and applied and wrote a logic as below

IF OBJECT_ID('tempdb..#FEEDBACK', 'U') IS NOT NULL
DROP TABLE #FEEDBACK

IF OBJECT_ID('tempdb..##FINALRES', 'U') IS NOT NULL
DROP TABLE ##FINALRES

CREATE TABLE #FEEDBACK(SECTION VARCHAR(25),SUBSECTION VARCHAR(5))
INSERT INTO #FEEDBACK
SELECT 'PUNCHUALITY','A1' UNION ALL
SELECT 'CONCEPTUAL','A1' UNION ALL
SELECT 'PUNCHUALITY','A2' UNION ALL
SELECT 'CONCEPTUAL','A2' UNION ALL
SELECT 'PUNCHUALITY','A3' UNION ALL
SELECT 'CONCEPTUAL','A3' UNION ALL
SELECT 'PUNCHUALITY','A4' UNION ALL
SELECT 'CONCEPTUAL','A4' UNION ALL
SELECT 'CONCEPTUAL','A5' UNION ALL
SELECT 'PUN1','A1' UNION ALL
SELECT 'PUN1','A2' UNION ALL
SELECT 'PUN1','A3' UNION ALL
SELECT 'CON1','A1'

--SELECT ROW_NUMBER() OVER(PARTITION BY SECTION ORDER BY SECTION,SUBSECTION)RNO,SECTION,SUBSECTION FROM #FEEDBACK
DECLARE @TEMP INT,@CNT INT,@SECTIONNAME VARCHAR(25)
SET @CNT = 0

SELECT @CNT = MAX(A.RNO) FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY SECTION ORDER BY SECTION,SUBSECTION)RNO,SECTION,SUBSECTION FROM #FEEDBACK)A

SELECT @SECTIONNAME = SECTION FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY SECTION ORDER BY SECTION,SUBSECTION)RNO,SECTION,SUBSECTION FROM #FEEDBACK)A
WHERE A.RNO = @CNT

DECLARE @COLLIST VARCHAR(1000)
SET @COLLIST = ''
SELECT @COLLIST = @COLLIST + A.SECTION + ' VARCHAR(25),' FROM
(SELECT DISTINCT SECTION FROM #FEEDBACK) A

SET @COLLIST=SUBSTRING(@COLLIST,1,LEN(@COLLIST)-1)
EXEC('CREATE TABLE ##FINALRES(ID INT IDENTITY(1,1),'+ @COLLIST+')'+
' INSERT INTO ##FINALRES('+@SECTIONNAME+')'+
' SELECT SUBSECTION FROM #FEEDBACK WHERE SECTION = '''+ @SECTIONNAME +'''')

DECLARE @SECTIONLIST TABLE(ID INT IDENTITY (1,1),SECTION VARCHAR(25))
INSERT INTO @SECTIONLIST(SECTION)
SELECT DISTINCT SECTION FROM #FEEDBACK WHERE SECTION <> @SECTIONNAME
SET @TEMP = 1
WHILE @TEMP <= (SELECT MAX(ID) FROM @SECTIONLIST)
BEGIN
SELECT @SECTIONNAME = SECTION FROM @SECTIONLIST WHERE ID = @TEMP

EXEC('UPDATE ##FINALRES SET ' +@SECTIONNAME+ '=V.SUBSEC FROM' +
'(
SELECT ROW_NUMBER() OVER(PARTITION BY SECTION ORDER BY SECTION,SUBSECTION)RNO,SECTION SEC,SUBSECTION SUBSEC FROM #FEEDBACK
WHERE SECTION ='''+@SECTIONNAME+'''
)V WHERE ID = V.RNO'
)

SET @TEMP = @TEMP + 1
END

DECLARE @DISPCOLS VARCHAR(1000)
SET @DISPCOLS = ''
SELECT @DISPCOLS = @DISPCOLS + A.SECTION + ',' FROM
(SELECT DISTINCT SECTION FROM #FEEDBACK) A
SET @DISPCOLS=SUBSTRING(@DISPCOLS,1,LEN(@DISPCOLS)-1)
EXEC('SELECT '+@DISPCOLS+' FROM ##FINALRES')
DROP TABLE ##FINALRES
DROP TABLE #FEEDBACK

Solutions are easy. Understanding the problem, now, that's the hard part

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-18 : 06:38:07

TRY THIS

DECLARE @FEEDBACK TABLE(SECTION VARCHAR(25),SUBSECTION VARCHAR(5))
INSERT INTO @FEEDBACK
SELECT 'PUNCHUALITY','A1' UNION ALL
SELECT 'CONCEPTUAL','A1' UNION ALL
SELECT 'PUNCHUALITY','A2' UNION ALL
SELECT 'CONCEPTUAL','A2' UNION ALL
SELECT 'PUNCHUALITY','A3' UNION ALL
SELECT 'CONCEPTUAL','A3' UNION ALL
SELECT 'PUNCHUALITY','A4' UNION ALL
SELECT 'CONCEPTUAL','A4'

;WITH CTE AS
(Select Case When Section='Punchuality' Then Subsection End As PUNCHUALITY,
Case When Section ='Conceptual' Then Subsection End As CONCEPTUAL
from @FEEDBACK )
sELECT C.CONCEPTUAL,F.PUNCHUALITY FROM CTE AS C JOIN CTE AS F ON C.CONCEPTUAL=F.PUNCHUALITY

In Love... With Me!

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2011-05-18 : 06:56:45

Hi raghu,

There will be any number of sections, in the above query you hard coded two section names, but it will not work if we have more than 2 section names.

Solutions are easy. Understanding the problem, now, that's the hard part

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 08:47:16

I like the way it rhymes! "Doubt in Pivot!!" Say it out loud 5 times

Can you do it with a simple pivot like this?

SELECT
	section,[a1],[a2]
FROM
	@FEEDBACK
PIVOT
(MAX(subsection) FOR subsection IN ([a1],[a2])) P

If you have an unknown number of subsections, you would need dynamic pivot as explained in Madhivanan's blog: http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources