Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select dynamic rows as columns?

Posted on 2016-07-14
3
Medium Priority
?
96 Views
Last Modified: 2016-08-03
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
0
Comment
Question by:thinklings
3 Comments
 
LVL 6

Assisted Solution

by:Manuel Marienne-DuchĂȘne
Manuel Marienne-DuchĂȘne earned 1000 total points
ID: 41711764
This is a dynamic pivot, you will find the explanation here :

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/
0
 
LVL 8

Expert Comment

by:Dung Dinh
ID: 41711836
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
 
LVL 8

Accepted Solution

by:
Jose Torres earned 1000 total points
ID: 41717616
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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question