Select dynamic rows as columns?

thinklings
thinklings used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is a dynamic pivot, you will find the explanation here :

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/
Dung DinhDBA and Business Intelligence Developer

Commented:
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

Certified Database Administrator
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial