Dynamically pivot a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamically pivot a table

Author

Topic

sqltenderfoot
Starting Member

5 Posts

Posted - 2010-11-03 : 06:11:37

Hi Guru's

I am trying to do the following.
I have a temp table with an undefined number of rumber of rows like:

Customer;Date;Product;Amount;
Cust001;2010-11-01;Shoes;100;
Cust001;2010-11-02;Shoes;100;
Cust001;2010-11-03;Socks;5;
Cust002;2010-11-01;Shoes;85;
Cust002;2010-11-03;Socks;6;
Cust003;2010-11-01;Shoes;80;
Cust004;2010-11-03;Socks;4;

Based on this I want to create the following temp table:
Customer;Date1;Product1;Amount1;Date2;Product2;Amount2;Date3;Product3;Amount3;Total
Cust001;2010-11-01;Shoes;100;2010-11-02;Shoes;100;2010-11-03;Socks;5;205;
Cust002;2010-11-01;Shoes;85;2010-11-03;Socks;6;;;;91;
Cust003;2010-11-01;Shoes;80;;;;;;;80;
Cust004;2010-11-03;Socks;4;;;;;;;4;

I have been going through some notes and blogs on this, there are quite a lot,
but I am strugling to get it working.

I can use some help on this. Thanks in advance.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-03 : 06:21:03

This is the easiest example to follow:

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

All you need to do is copy run through this example to see what it is doing.

You will need to work out your total before you pivot though.

If you are still stuck after following that example, please say where you are stuck exactly..

sqltenderfoot
Starting Member

5 Posts

Posted - 2010-11-03 : 07:23:16

Hi Rick,

Thanks for the reply.
Currently I have the code (see below)
I am stuck at filling table2 based on my original data in the correct way. How can I best do that?

Drop TABLE T_Input
Drop TABLE Table1
Drop TABLE Table2

CREATE TABLE T_INPUT
(CustId varchar(25),
Date varchar(25),
ProductName varchar (25),
Amount money Null)

GO

INSERT INTO T_INPUT VALUES ('Cust001', '2010-11-01', 'Shoes', 100)
INSERT INTO T_INPUT VALUES ('Cust001', '2010-11-02', 'Shoes', 100)
INSERT INTO T_INPUT VALUES ('Cust001', '2010-11-03', 'Socks', 5)
INSERT INTO T_INPUT VALUES ('Cust002', '2010-11-01', 'Shoes', 85)
INSERT INTO T_INPUT VALUES ('Cust002', '2010-11-01', 'Socks', 6)
INSERT INTO T_INPUT VALUES ('Cust003', '2010-11-01', 'Shoes', 80)
INSERT INTO T_INPUT VALUES ('Cust004', '2010-11-03', 'Socks', 4)

GO

Create Table Table1 (ColId INT, ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Customer')
INSERT INTO Table1 VALUES(2, 'Date1')
INSERT INTO Table1 VALUES(3, 'Product1')
INSERT INTO Table1 VALUES(4, 'Amount1')
INSERT INTO Table1 VALUES(5, 'Date2')
INSERT INTO Table1 VALUES(6, 'Product2')
INSERT INTO Table1 VALUES(7, 'Amount2')
INSERT INTO Table1 VALUES(8, 'Date3')
INSERT INTO Table1 VALUES(9, 'Product3')
INSERT INTO Table1 VALUES(10, 'Amount3')
INSERT INTO Table1 VALUES(11, 'Total')

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

-- How to fill table 2 correctly based on table T_Input?

DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + colName + ']',
'[' + colName + ']')
FROM Table1
ORDER BY colName

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT t2.tID
, t1.ColName
, t2.Txt
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

EXECUTE(@query)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources