Ashish Patel

How to Convert rows into columns using Pivot in SQL Server(Query)

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
In Sql, Pivoting can convert rows to columns. We will see these features in details in this post.
For example, we are considering a table named Ashish.

want to perform a sql query to return results like this:


Here is SQL Query:
First, Create table called ashish..

CREATE TABLE [dbo].[ashish](
    [custID] [int] NULL,
    [Question] [varchar](50) NULL,
    [answer] [varchar](20) NULL
) ON [PRIMARY]

Then, insert some values..

 
INSERT INTO ashish values(1000,    'AAA',    '1')
INSERT INTO ashish values(1000,    'BBB B',    '2')
INSERT INTO ashish values(1000,    'CCC',    '3')
INSERT INTO ashish values(1001,    'AAA',    '2')
INSERT INTO ashish values(1001,    'BBB B',    '3')
INSERT INTO ashish values(1001,    'CCC',    '3')
INSERT INTO ashish values(1000,    'DDD',    '6')

here is pivot query to convert rows to columns..

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 ashish) cur
-- removing last ',' from both variables
SET @B=SUBSTRING(@B,1,LEN(@B)-1)
SET @A=@A+ + ' FROM (SELECT CustID, Answer, Question FROM ashish) s  PIVOT (max(answer) FOR Question IN ' +@B+')) p ORDER BY [CUSTID]; '
exec(@A);

Thanks

Posted in SQL ServerTagged , , , 8 Comments

8 responses to “How to Convert rows into columns using Pivot in SQL Server(Query)”

  5. Same problem i have
    but i have a date instead of question

    could you please send me the query for the same.

    Reply

    • i have a table and data like this

      P_P_Date Project_Id Doc_No
      13/12/2014 PPW 101
      13/12/2014 UW 102
      13/12/2014 VW 103
      13/12/2014 SMI 104
      14/12/2015 PPW 101
      14/12/2015 UW 102
      14/12/2015 VW 103
      14/12/2015 SMI 104
      15/12/2015 PPW 105
      15/12/2015 UW 106
      15/12/2015 VW 107
      15/12/2015 SMI 108
      16/12/2015 PPW 105
      16/12/2015 UW 106
      16/12/2015 VW 107
      16/12/2015 SMI 108

      I want this data in this table format
      Project_Id 13/12/2014 14/12/2015 14/12/2015 16/12/2015
      PPW 101 101 105 105
      UW 102 102 106 106
      VW 103 103 107 107
      SMI 104 104 108 108

      Please send me the query for it.

      Reply

      • CREATE TABLE [dbo].[ashish] (
        P_P_Date nvarchar(10) NOT NULL,
        Project_Id nvarchar(10) NOT NULL,
        Doc_No int not null
        );

        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('13/12/2014','PPW','101')

        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('13/12/2014','UW','102')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('13/12/2014','VW','103')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('13/12/2014','SMI','104')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('14/12/2015','PPW','101')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('14/12/2015','UW','102')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('14/12/2015','VW','103')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('14/12/2015','SMI','104')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('15/12/2015','PPW','105')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('15/12/2015','UW','106')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('15/12/2015','VW','107')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('15/12/2015','SMI','108')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('16/12/2015','PPW','105')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('16/12/2015','UW','106')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('16/12/2015','VW','107')
        insert into ashish (P_P_Date,Project_Id,Doc_No) values ('16/12/2015','SMI','108')

        Declare @t VARCHAR(10)
        Declare @A VARCHAR(1000)
        Declare @B VARCHAR(1000)
        set @A='SELECT Project_Id'
        SET @B='('
        SELECT @A=@A+',['+P_P_Date+'] as [' +P_P_Date+']',@B=@B+'['+P_P_Date+'],' FROM (SELECT DISTINCT P_P_Date FROM ashish) cur
        -- removing last ',' from both variables
        SET @B=SUBSTRING(@B,1,LEN(@B)-1)
        SET @A=@A+ + ' FROM (SELECT P_P_Date, Project_Id, Doc_No FROM ashish) s PIVOT (max(Doc_No) FOR P_P_Date IN ' +@B+')) p ORDER BY [Project_Id]; '
        exec(@A);

        Reply

  6. Dear :
    There are a lot of problems that I see So please help Me
    I Run This Script :
    Declare @t VARCHAR(10)
    Declare @A VARCHAR(1000)
    Declare @B VARCHAR(1000)
    set @A=’SELECT F_SN’
    SET @B='(‘
    SELECT @A=@A+’,[‘+F_Titel+’] as [‘ +F_Titel+’]’,@B=@B+'[‘+F_Titel+’],’ FROM (SELECT DISTINCT F_Titel FROM T_GetDataFormXls) cur
    — removing last ‘,’ from both variables
    SET @B=SUBSTRING(@B,1,LEN(@B)-1)
    SET @A=@A+ + ‘ FROM (SELECT F_SN, F_Data, F_Titel FROM T_GetDataFormXls) PIVOT (max(F_Data) FOR F_Titel IN ‘ +@B+’)) p ORDER BY [F_SN]; ‘
    exec(@A);

    and I have this error:
    Msg 1038, Level 15, State 4, Line 1
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 1
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘PIVOT’.
    Msg 1038, Level 15, State 4, Line 1
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.

    Reply

Leave a Reply