SQL Query for PIVOT (Convert rows into columns)

Hi Reader,

I am providing you a simple logic to convert rows values into columns using PIVOT concept in SQL Server with example.

CREATE TABLE [dbo].[#TestAshok](

    [custID] [int] NULL,

    [Question] [varchar](50) NULL,

    [answer] [varchar](20) NULL

) ON [PRIMARY]

INSERT INTO [#TestAshok] values(1000,    'AAA',    '1')

INSERT INTO [#TestAshok] values(1000,    'BBB B',    '2')

INSERT INTO [#TestAshok] values(1000,    'CCC',    '3')

INSERT INTO [#TestAshok] values(1001,    'AAA',    '2')

INSERT INTO [#TestAshok] values(1001,    'BBB B',    '3')

INSERT INTO [#TestAshok] values(1001,    'CCC',    '3')

INSERT INTO [#TestAshok] values(1000,    'DDD',    '6')

select * from #TestAshok

Declare @t VARCHAR(10)

Declare @A VARCHAR(1000)

Declare @B VARCHAR(1000)

set @A='SELECT CustID'

SET @B='('

SELECT @A=@A+',['+Question+'] as [' +Question+']',@B=@B+'['+Question+'],' FROM (SELECT DISTINCT QUESTION FROM #TestAshok) cur

-- removing last ',' from both variables

SET @B=SUBSTRING(@B,1,LEN(@B)-1)

SET @A=@A+ + ' FROM (SELECT CustID, Answer, Question FROM #TestAshok) s  PIVOT (max(answer) FOR Question IN ' +@B+')) p ORDER BY [CUSTID]; '

--select @A

exec(@A);