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.
Author |
Topic |
Petronas
Posting Yak Master
134 Posts |
Posted - 2013-12-06 : 14:26:53
|
Hi, I have a following table: Customer Id Billing Amt Bill Sent Mnth Bill sent date100391 86.55 201302 2/18/2013100391 79.95 201304 4/8/2013100391 65.75 201305 5/13/201372331 15.95 201301 1/12/201372331 24.95 201303 3/3/2013I need to display is as Customer ID 201301 201302 201303 201304 201305 201306 201307100391 2/8/2013 4/8/2013 5/13/201372331 1/12/2013 3/3/2013 I have to put the Bill Sent Mnths as Columns and have the Bill sent date for each corresponding customer_id displayed under it. Thank you ,Petronas |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-07 : 04:12:50
|
[code]DECLARE @BillMonthList varchar(5000),@SQLQuery varchar(max)SELECT @BillMonthList = STUFF((SELECT DISTINCT ',[' + CAST([Bill Sent Mnth] AS varchar(6)) + ']' FROM Table ORDER BY ',[' + CAST([Bill Sent Mnth] AS varchar(6)) + ']' FOR XML PATH('')),1,1,'')SET @SQLQuery='SELECT Customer_ID,' + @BillMonthList +' FROM(SELECT t.*,t1.[Bill sent date]FROM(SELECT m.BillMonth,n.Customer_IDFROM (SELECT DISTINCT [Bill Sent Mnth] AS BillMonth FROM Table)mCROSS JOIN (SELECT DISTINCT Customer_ID FROM Table)n)tLEFT JOIN Table t1ON t1.[Bill Sent Mnth] = t.BillMonth AND t1.Customer_ID = t.Customer_ID )oPIVOT (MAX([Bill sent date]) FOR BillMonth IN (' + @BillMonthList + '))p'EXEC (@SQLQuery)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|