Solved

Select dynamic rows as columns?

Posted on 2016-07-14
3
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Assisted Solution

by:Manuel Marienne-DuchĂȘne
Manuel Marienne-DuchĂȘne earned 250 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 250 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

729 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