Select dynamic rows as columns?

Hi,

I have a CTE that selects two sets of data. One called Months that will return back the last year worth of months, for example:

2015-07
2015-08
2015-09
2015-10
2015-11
2015-12
2016-01
2016-02
2016-03
2016-04
2016-05
2016-06
2016-07

Another, contains the core data that returns data in the below column format:

ClientID      MonthPeriod      Spending
0001            2015-7                 120.11
0001            2015-9                 190.34
0001            2015-10               100.10
0001            2016-2                 140.05

How do i query the above to so that it returns in the following format dynamically:

ClientID      2015-7      2015-8      2015-9      2015-10      2015-11      2015-12      2016-01      2016-02
0001           120.11       0.00          190.34       100.10         0.00            0.00             0.00             140.05
thinklingsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Manuel Marienne-DuchêneITMCommented:
This is a dynamic pivot, you will find the explanation here :

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
The PIVOT is one of options for you. You can try with below before applying the dynamic PIVOT
WITH Months([Month] ,MonthOrder) As
(
 SELECT '2015-07',1
 UNION
 SELECT '2015-08',2
 UNION
 SELECT '2015-09',3
 UNION
 SELECT '2015-10',4
 UNION 
 SELECT '2015-11',5
 UNION
 SELECT '2015-12',6
 UNION
 SELECT '2016-01',7
 UNION
 SELECT '2016-02',8
 UNION
 SELECT '2016-03',9
 UNION
 SELECT '2016-04',10
 UNION
 SELECT '2016-05',11
 UNION
 SELECT '2016-06',12
 UNION
 SELECT '2016-07',13
)
,Sales(CustomerID,[Month],SalesAmount) AS
(
 SELECT '0001','2015-07',120.11
 UNION
 SELECT '0001','2015-09',190.34
 UNION
 SELECT '0001','2015-10',100.10
 UNION
 SELECT '0001','2016-02',140.05
 UNION
 SELECT '0002','2015-09',150.05 
)
,Report(CustomerID,[Month],SalesAmount) AS
(
   
   SELECT tmp.CustomerID,tmp.Month,ISNULL(s.SalesAmount,0.00) As SalesAmount
   FROM 
   (
       SELECT s.CustomerID,m.Month,0.00  SalesAMount
       FROM (SELECT DISTINCT CustomerID FROM Sales) s
           CROSS JOIN Months m 
   ) tmp
   LEFT JOIN Sales s ON tmp.CustomerID = s.CustomerID AND tmp.Month = s.Month
)
SELECT [CustomerID], [2015-07],[2015-08],[2015-09],[2015-10],[2015-11],[2015-12],[2016-01],
          [2016-02],[2016-03],[2016-04],[2016-05],[2016-06],[2016-07]
FROM Report r
     PIVOT
     (
       SUM(r.SalesAmount)
       FOR r.Month IN ([2015-07],[2015-08],[2015-09],[2015-10],[2015-11],[2015-12],[2016-01],
                       [2016-02],[2016-03],[2016-04],[2016-05],[2016-06],[2016-07])
     ) AS pv;

Open in new window

0
Jose TorresSenior SQL Server DBACommented:
Okay,

I am not discounting pivot.  But just to give an additional option.
Remember I am from an era where the dinosaur computers roamed the lands.

DECLARE @MonthsLookBack INT = 12
DECLARE @ColumnName NVARCHAR(128)
DECLARE @SQLCmd NVARCHAR(MAX)
DECLARE @SQLTxt1 NVARCHAR(MAX) = '
	ALTER TABLE #Results
	ADD [ParmColumn] DECIMAL(18,2) DEFAULT 0.00'
DECLARE @SQLTxt2 NVARCHAR(MAX) = '
	UPDATE #Results SET [ParmColumn] = 0.00'
DECLARE @SQLTxt3 NVARCHAR(MAX) = '
	UPDATE R
	SET R.[ParmColumn] = S.SumSpending
	FROM #Results R
	INNER JOIN (
		SELECT ClientID, SumSpending = SUM(Spending) 
		FROM #SourceData
		WHERE MonthPeriod = ''ParmColumn''
		GROUP BY ClientID
		) S ON R.ClientID = S.ClientID
'

IF OBJECT_ID('tempdb..#SourceData') IS NOT NULL DROP TABLE #SourceData
CREATE TABLE #SourceData (
	ClientID NVARCHAR(50),
	MonthPeriod NVARCHAR(50),
	Spending DECIMAL(18,2)
)
INSERT INTO #SourceData (ClientID,MonthPeriod,Spending) VALUES
('0001','2015-7',120.11),
('0001','2015-9',190.34),
('0001','2015-10',100.10),
('0001','2016-2',140.05)

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
	ClientID NVARCHAR(50)
)

INSERT INTO #Results (ClientID)
SELECT DISTINCT ClientID FROM #SourceData ORDER BY ClientID

WHILE @MonthsLookBack >= 0
BEGIN
	SET @ColumnName = FORMAT(DATEADD(MONTH,-@MonthsLookBack,GETDATE()),'yyyy-M')
	SET @SQLCmd = REPLACE(@SQLTxt1,'ParmColumn',@ColumnName)
	EXEC sp_executesql @SQLCmd
	SET @SQLCmd = REPLACE(@SQLTxt2,'ParmColumn',@ColumnName)
	EXEC sp_executesql @SQLCmd
	SET @SQLCmd = REPLACE(@SQLTxt3,'ParmColumn',@ColumnName)
	EXEC sp_executesql @SQLCmd
	SET @MonthsLookBack = @MonthsLookBack - 1
END

SELECT * FROM #Results

Open in new window

And here are the results
Output
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.