none
Query for Transpose Data

    Question

  • hi,

      I have a table with the following data

    ItemName   Version Quantity

    I1                     1        10

    I2                      2        15

    I3                      1          8

    I4                       2          9

    I need to transpose like below

    ItemName    Quantity1   Quantity2

    I1                       10          15

    I2                        8            9

    Quantity1 , Quantity2 etc based on version

     

    Monday, July 01, 2013 9:43 AM

Answers

  • Here  you go

    CREATE TABLE Foo (
      ItemName CHAR(2),
      Ver TINYINT,
      qty INT);

    INSERT INTO Foo  VALUES('I1', 1,10);
    INSERT INTO Foo VALUES('I1', 2,15);
    INSERT INTO Foo VALUES('I2', 1,18);
    INSERT INTO Foo VALUES('I2', 2,9);

    ALTER TABLE Foo ADD id INT IDENTITY(1,1)

    -- dynamic pivot (SQL Server 2005/2008/2012)
    DECLARE @pivot_cols_Aggr NVARCHAR(1000);
    DECLARE @pivot_cols NVARCHAR(1000);

    SELECT @pivot_cols_Aggr =
            STUFF((SELECT DISTINCT ']),MAX([' + CAST(Ver AS VARCHAR(10)) 
                   FROM Foo
                   ORDER BY ']),MAX([' + CAST(Ver AS VARCHAR(10)) 
                   FOR XML PATH('')
                   ), 1, 2, '') + '])';

    SET  @pivot_cols_Aggr=SUBSTRING(@pivot_cols_Aggr,2,LEN(@pivot_cols_Aggr))

    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + CAST(Ver AS VARCHAR(10)) 
                   FROM Foo
                   ORDER BY '],[' + CAST(Ver AS VARCHAR(10)) 
                   FOR XML PATH('')
                   ), 1, 2, '') + '] ';


    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ItemName,' + @pivot_cols_Aggr +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(qty) FOR Ver IN (' + @pivot_cols + ')) AS P GROUP BY ItemName;';

    EXEC(@pivot_query);

    DROP TABLE Foo;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by akhilrajau Thursday, July 04, 2013 10:50 AM
    Thursday, July 04, 2013 10:44 AM
    Answerer

All replies

  • SELECT Version,

    SUM(CASE WHEN Version=1 THEN Quantity END) Quantity_1,

    SUM(CASE WHEN Version=2 THEN Quantity END) Quantity_2

    FROM tbl 

    GROUP BY Version


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 01, 2013 9:48 AM
    Answerer
  • what is the logic to find out ItemName .

    Monday, July 01, 2013 9:49 AM
  • > PIVOT 

    Static Pivot:

    -- code 1
    select ItemName, [1] as Quantity1, [2] as Quantity2 from (select ItemName, Version, Quantity from TabV) as S pivot( sum(Quantity) for Version in ([1],[2])) as P;

    Belo Horizonte, MG - Brasil

    • Edited by Jose.Diz Monday, July 01, 2013 10:36 AM code 1
    Monday, July 01, 2013 9:49 AM
  • thats fine but if version is more than fixed how can i dynamically write it
    Monday, July 01, 2013 9:56 AM
  • DYNAMICALLY

    -- structure and data
    use tempdb; create table TabV ( ItemName varchar(20), Version int, Quantity int ); insert into TabV values ('I1',1,10), ('I2',2,15), ('I3',1,8), ('I4',2,9);
    select * from TabV;

    -- code 2 declare @ListaCol varchar(500); set @ListaCol=''; select @ListaCol += '['+ cast([Version] as varchar) +'],' from (select distinct [Version] from TabV) as T; set @ListaCol=left(@ListaCol, datalength(@ListaCol)-1); --print @ListaCol; -- declare @ComandoSQL varchar(1500); set @ComandoSQL = 'select ItemName,' + @ListaCol + ' from TabV pivot (sum(Quantity) ' + 'for [Version] in ('+@ListaCol+')) as P;'; --print @ComandoSQL; execute (@ComandoSQL);

    Mark as answered if this post solved your problem and Vote as helpful if this post was useful.
    • Edited by Jose.Diz Tuesday, July 02, 2013 7:22 PM order by
    Monday, July 01, 2013 10:00 AM
  • thanks. but the sp returns duplicate records for each version

    without dynamic sp the records are correct but when i use the above dynamic sp the records duplicated like below

    ItemName [1]     [2]

    I1               10     null

    I1               null      8 

    Monday, July 01, 2013 10:22 AM
  • If you need transpose to ItemName, Quantity1, Quantity2, ...

    -- code 2
    declare @ListaCol varchar(500); set @ListaCol=''; select @ListaCol += '['+ cast([Version] as varchar) +'],' from (select distinct [Version] from TabV) as T; set @ListaCol=left(@ListaCol, datalength(@ListaCol)-1); -- declare @ComandoSQL varchar(1500); set @ComandoSQL = 'select ItemName,' + @ListaCol + ' from TabV pivot (sum(Quantity) ' + 'for [Version] in ('+@ListaCol+')) as P;'; execute (@ComandoSQL);


    If you need transpose to Version, Quantity1, Quantity2, ...

    -- code 3
    declare @ListaCol varchar(3500); set @ListaCol=''; select @ListaCol += '['+ItemName+'],' from (select distinct ItemName from TabV) as T; set @ListaCol=left(@ListaCol, datalength(@ListaCol)-1); -- declare @ComandoSQL varchar(8000); set @ComandoSQL = 'select [Version],'+ @ListaCol + ' from TabV pivot (sum(Quantity) ' + 'for ItemName in ('+@ListaCol+')) as P;'; execute (@ComandoSQL);

    code 3 results
    Version	I1	I2	I3	I4
    1	      10	NULL	8	NULL
    2	      NULL	15	NULL	9


    Fui...


    Belo Horizonte, MG - Brasil
    Mark as answered if this post solved your problem and Vote as helpful if this post was useful.

    • Edited by Jose.Diz Tuesday, July 02, 2013 7:21 PM order by
    Monday, July 01, 2013 10:54 AM
  • thats ok but i want the result without duplicate

    like the query given by Uri Dimant. but this is static.

    The needed output is

    ItemName Qty1  Qty2

    I1               10      11

    I2                8       null

    Here you can see that I1 is only one time with 2 version quantities. For I2 i have only version1 so the value only for Qty1 

    Monday, July 01, 2013 11:19 AM
  • You can build dynamic sql , see example

    CREATE TABLE Foo (
      foo_type CHAR(1) PRIMARY KEY,
      foo_value INT);

    INSERT INTO Foo VALUES('A', 1);
    INSERT INTO Foo VALUES('B', 2);
    INSERT INTO Foo VALUES('C', 3);
    INSERT INTO Foo VALUES('D', 4);

    -- pivot using CASE
    SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A,
            MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B,
            MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C,
            MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D
    FROM Foo;

    -- pivot using PIVOT operator (SQL Server 2005/2008)
    SELECT A, B, C, D
    FROM Foo
    PIVOT
    (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

    -- dynamic pivot (SQL Server 2005/2008)
    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + foo_type
                   FROM Foo
                   ORDER BY '],[' + foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ' + @pivot_cols +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);

    DROP TABLE Foo;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 01, 2013 11:29 AM
    Answerer
  • If the data is  as follows

    ItemName   Version Quantity

    I1                     1        10

    I1                      2        15

    I2                      1          8

    I2                       2          9

    I need to transpose like below

    ItemName    Quantity1   Quantity2

    I1                       10          15

    I2                        8            9

    Quantity1 , Quantity2 etc based on version

    Tuesday, July 02, 2013 1:08 PM
  • SELECT ItemName   ,

    MAX(CASE WHEN Version =1 THEN Quantity END),

    MAX(CASE WHEN Version =2 THEN Quantity END)

    FROM tbl GROU BY ItemName   


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 02, 2013 1:17 PM
    Answerer
  • thanks. But version is not fixed how can i dynamically write it.

    Wednesday, July 03, 2013 6:26 AM
  • Take a look at example I posted above how to use dynamic pivoting.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 03, 2013 6:59 AM
    Answerer
  • But it showing duplicate entries as follows

    ItemName [1]     [2]

    I1               10     null

    I1               null      8 

    l2                 8      null

    l2               null       9

    But i need Output as follows

    ItmName  [1]   [2]

    l1             10     8

    l2              8      9

    Wednesday, July 03, 2013 8:33 AM
  • It works just fine

    CREATE TABLE Foo (
      ItemName CHAR(2),
      Ver TINYINT,
      qty INT);

    INSERT INTO Foo VALUES('I1', 1,10);
    INSERT INTO Foo VALUES('I1', 2,15);
    INSERT INTO Foo VALUES('I2', 1,18);
    INSERT INTO Foo VALUES('I2', 2,9);


    -- dynamic pivot (SQL Server 2005/2008/2012)
    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + CAST(Ver AS VARCHAR(10)) 
                   FROM Foo
                   ORDER BY '],[' + CAST(Ver AS VARCHAR(10)) 
                   FOR XML PATH('')
                   ), 1, 2, '') + '] ';


    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ItemName,' + @pivot_cols +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(qty) FOR Ver IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);

    DROP TABLE Foo;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 03, 2013 8:51 AM
    Answerer
  • But I am Getting output like this

    ItemName         1                    2

    l1                 800.0000             NULL

    l2                  810.0000            NULL 

    l1                     NULL                 900.0000 

    l2                     NULL                 910.0000 

    Wednesday, July 03, 2013 11:19 AM
  • I used (you see that) the test data you have posted above, there is no miracle.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 03, 2013 11:59 AM
    Answerer
  • TRY THIS,

    declare @TempTable table (ItemName varchar(20), Version int,  Quantity int);
    insert into @TempTable values('I1',1,10)
    insert into @TempTable values('I1',2,15) 
    insert into @TempTable values('I2',1,8)
    insert into @TempTable values('I2',2,9)
    ---------- final query
    select itemname,sum(quantity1) as Quantity1,sum(quantity2) as Quantity2 from (
    	select itemname,Quantity1,Quantity2 from (
    		select *,'Quantity' + cast(version as nvarchar) as QuantityCol from @TempTable
    	) x
    	pivot
    	(
    	max(Quantity) for QuantityCol in (Quantity1,Quantity2)
    	) pvt
    ) Y
    group by itemname


    Regards, RSingh

    Wednesday, July 03, 2013 3:15 PM
  • And also i have one identity column in the given table. Now check the query pls

    Thursday, July 04, 2013 10:32 AM
  • Here  you go

    CREATE TABLE Foo (
      ItemName CHAR(2),
      Ver TINYINT,
      qty INT);

    INSERT INTO Foo  VALUES('I1', 1,10);
    INSERT INTO Foo VALUES('I1', 2,15);
    INSERT INTO Foo VALUES('I2', 1,18);
    INSERT INTO Foo VALUES('I2', 2,9);

    ALTER TABLE Foo ADD id INT IDENTITY(1,1)

    -- dynamic pivot (SQL Server 2005/2008/2012)
    DECLARE @pivot_cols_Aggr NVARCHAR(1000);
    DECLARE @pivot_cols NVARCHAR(1000);

    SELECT @pivot_cols_Aggr =
            STUFF((SELECT DISTINCT ']),MAX([' + CAST(Ver AS VARCHAR(10)) 
                   FROM Foo
                   ORDER BY ']),MAX([' + CAST(Ver AS VARCHAR(10)) 
                   FOR XML PATH('')
                   ), 1, 2, '') + '])';

    SET  @pivot_cols_Aggr=SUBSTRING(@pivot_cols_Aggr,2,LEN(@pivot_cols_Aggr))

    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + CAST(Ver AS VARCHAR(10)) 
                   FROM Foo
                   ORDER BY '],[' + CAST(Ver AS VARCHAR(10)) 
                   FOR XML PATH('')
                   ), 1, 2, '') + '] ';


    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ItemName,' + @pivot_cols_Aggr +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(qty) FOR Ver IN (' + @pivot_cols + ')) AS P GROUP BY ItemName;';

    EXEC(@pivot_query);

    DROP TABLE Foo;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by akhilrajau Thursday, July 04, 2013 10:50 AM
    Thursday, July 04, 2013 10:44 AM
    Answerer
  • Thank you
    Thursday, July 04, 2013 10:50 AM
  • How to Renmae a column names dynamically
    Tuesday, August 06, 2013 8:25 AM
  • How To rename a column names Dynamically as version1,varsion2 as etc

    Tuesday, August 06, 2013 8:34 AM