How to 'flatten' a table? 2

[crystalreporting]

I have a table with records like;

Customer Month 1 Month 2 Month 3...
-------- ------- ------- -------
AB001 100.00 120.00 98.00
BC001 212.00 126.00 88.00
.
.
.

I would like to create a view that would 'flatten' the table into;

Customer Month Amount
-------- ----- ------
AB001 1 100.00
AB001 2 120.00
AB001 3 98.00
BC001 1 212.00
.
.
.

Any ideas how I build the structure to accomplish this?

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com

 

[gmmastros]

Select Customer, 1 As Month, Month_1 As Amount
From   MyTable

Union All

Select Customer, [!]2[/!], Month_[!]2[/!]
From   MyTable

Union All

Select Customer, [!]3[/!], Month_[!]3[/!]
From   MyTable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

but better may be to normalize that table

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[crystalreporting]

Thanks gmmastros - works perfectly!

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com

 

[mrdenny]

If this is SQL 2005 or higher then you can use the UNPIVOT statement.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[BIExperts]

I would definately use UNPIVOT operator, because it geneates better execution plan:

SELECT Customer, Month, Amount
FROM
(SELECT Customer, [Month 1], [Month 2], [Month 3]
FROM pvt) p
UNPIVOT
(Amount FOR Month IN
('Month 1', 'Month 2', 'Month 3')
) AS unpvt

As Denny wrote, UNPIVOT is available in SQL Server starting from version 2005.