Grouping data using Pivot? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping data using Pivot?

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2011-04-19 : 11:49:55

Hi

I have a table with a number of date fields and amounts which I want to output as just two fields. The current format is shown below:

CustRef Date 1 AMT1 Date 2 AMT2 Date 3 AMT3
1002012 080411 1045 030411 1115 060411 1257
1003032 070411 1555 080411 1005 070411 1333

What I want is to output as:
CustRef Date AMT
1002012 080411 1045
1002012 030411 1115
1002012 060411 1257
1003032 070411 1555
1003032 080411 1005
1003032 070411 1333

Is there anyway that I can get the data out in this format using T-SQL?

Is there something like a Pivot command I can use?

Thanks in advance.

Dave

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-19 : 12:02:21

follow this example from MSDN
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

--------------------------
http://connectsql.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-19 : 12:03:07

select *
from
(
select CustRef, Date = Date1, Amt = AMT1 from tbl
union all
select CustRef, Date2, AMT2 from tbl
union all
select CustRef, Date3, AMT3 from tbl
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources