?
Solved

Select dynamic rows as columns?

Posted on 2016-07-14
3
Medium Priority
?
99 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

569 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