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

 

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

what is the logic to find out ItemName .

> 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		

thats fine but if version is more than fixed how can i dynamically write it

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.

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 

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...

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 

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;

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

SELECT ItemName   ,

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

MAX(CASE WHEN Version =2 THEN Quantity END)

FROM tbl GROU BY ItemName   

thanks. But version is not fixed how can i dynamically write it.

Take a look at example I posted above how to use dynamic pivoting.

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

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;

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 

I used (you see that) the test data you have posted above, there is no miracle.

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

And also i have one identity column in the given table. Now check the query pls

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;

Thank you

How to Renmae a column names dynamically

How To rename a column names Dynamically as version1,varsion2 as etc