Dynamic PIVOT quey challange

Hi

I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.

Table A

LotNo.

Pcs

Wt.

12

200

12.212

13

21

16.214

17

23

21.211

18

27

32.212

Table B

LotNo

12

13

17

18

Pcs

200

21

23

27

Weight

12.212

16.214

21.211

32.212

So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.

Nilkanth Desai

You can use the following querys

1. Using UNION

SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2

2. Merging data into Single Table then applying the Pivot

Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)

Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;

SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2

To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1

|||

Here you go:

-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;

-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;

-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);

SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));

-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'

-- Run it
EXEC sp_executesql @.sql;

-- Cleanup
DROP TABLE #T1;

sql