October 17, 2016 at 2:46 pm
Hello,
I'm a SQL Server novice. I've been polishing up on my T-SQL skills while working for the last few months and I was tasked to come up with a solution, so I've made some research and read some articles about dynamic pivot, dynamic sql and cursors, but I didn't get the desired result basically because I'm being overwhelmed. So, it would be deeply appreciated if someone can explain those with some minor examples.
On the other hand, the thing I'm going to try is to transpose the dynamic rows into columns with dynamic pivot or something that meets my needs.
Suppose I have a data as follows. What I'm trying to do is to transpose the dynamic rows into columns, so I'm unable to use the static pivot to solve this, that's why I've made some research about dynamic sql, but some detailed examples would be great in understanding those methods.
Thanks in advance.
October 17, 2016 at 11:14 pm
Take a look at the accepted reponse at "http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query"
You will need to replace "date" with your list of static columns (product, area, etc)
October 18, 2016 at 4:38 am
@DesNorton, thank you so much for your help, the link you've provided above was pretty handy and helped me in understanding how I do it.
So, I came up with the following query;
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.WEEK)
FROM WEEKLY_SALES c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ' FROM
(
SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, WEEK, UNITS
FROM WEEKLY_SALES
) x
PIVOT
(
SUM(UNITS)
FOR WEEK IN (' + @cols + ')
) p '
EXECUTE (@query)
But it returns "NULL" values that I need to update with "0". So, how do I change the NULL values with 0 ?
Thanks,
seismicbeat
October 18, 2016 at 5:00 am
seismicbeat (10/18/2016)
@DesNorton, thank you so much for your help, the link you've provided above was pretty handy and helped me in understanding how I do it.So, I came up with the following query;
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.WEEK)
FROM WEEKLY_SALES c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ' FROM
(
SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, WEEK, UNITS
FROM WEEKLY_SALES
) x
PIVOT
(
SUM(UNITS)
FOR WEEK IN (' + @cols + ')
) p '
EXECUTE (@query)
But it returns "NULL" values that I need to update with "0". So, how do I change the NULL values with 0 ?
Thanks,
seismicbeat
You need to create 2 column lists - One for the PIVOT section, and one wrapped in ISNULL for the SELECT section.
DECLARE
@cols AS NVARCHAR(MAX),
@selcols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.WEEK)
FROM WEEKLY_SALES c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,''),
@selcols = STUFF((SELECT DISTINCT ',ISNULL(' + QUOTENAME(c.WEEK) + ', 0) AS ' + QUOTENAME(c.WEEK)
FROM WEEKLY_SALES c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
set @query = 'SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @selCols + ' FROM
(
SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, WEEK, UNITS
FROM WEEKLY_SALES
) x
PIVOT
(
SUM(UNITS)
FOR WEEK IN (' + @cols + ')
) p '
EXECUTE (@query)
October 18, 2016 at 5:48 am
seismicbeat (10/17/2016)
Hello,I'm a SQL Server novice. I've been polishing up on my T-SQL skills while working for the last few months and I was tasked to come up with a solution, so I've made some research and read some articles about dynamic pivot, dynamic sql and cursors, but I didn't get the desired result basically because I'm being overwhelmed. So, it would be deeply appreciated if someone can explain those with some minor examples.
On the other hand, the thing I'm going to try is to transpose the dynamic rows into columns with dynamic pivot or something that meets my needs.
Suppose I have a data as follows. What I'm trying to do is to transpose the dynamic rows into columns, so I'm unable to use the static pivot to solve this, that's why I've made some research about dynamic sql, but some detailed examples would be great in understanding those methods.
Thanks in advance.
Dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:
http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]
http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 18, 2016 at 9:57 am
@desnorton, @ChrisM@Work, I really appreciate to all your help. 🙂 Thank you so much for leading me to the right direction.
October 18, 2016 at 10:01 am
seismicbeat (10/18/2016)
@DesNorton, @ChrisM@Work, I really appreciate to all your help. 🙂 Thank you so much for leading me to the right direction.
You're welcome, thanks for the feedback. If you get stuck with your solution, post what you've got and folks will help you out - most of the lurkers around here are very familiar with Jeff's articles.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 18, 2016 at 5:24 pm
ChrisM@Work (10/18/2016)
seismicbeat (10/18/2016)
@DesNorton, @ChrisM@Work, I really appreciate to all your help. 🙂 Thank you so much for leading me to the right direction.You're welcome, thanks for the feedback. If you get stuck with your solution, post what you've got and folks will help you out - most of the lurkers around here are very familiar with Jeff's articles.
Heh... Me too! I know that guy! 🙂
Thanks, for the referral, Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2016 at 11:58 am
Hello,
Sorry for bothering you again. I have a couple of questions about putting the dynamic pivot results to a local table or view. I think, I can use INTO statement to put the results of a temp table by the following addition, but I don't know how it serves to my purpose, since I need to put the results into a local table that I can select.
set @query = 'SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @selCols + ' INTO ##temp FROM
I've read some blogs, but couldn't figure out. There were some explanations that saying using a local table is not possible, and examples with Views are a little bit complicated. So, it would be great if you can help me in understanding this ?
Thanks in advance
October 19, 2016 at 12:09 pm
seismicbeat (10/19/2016)
Hello,Sorry for bothering you again. I have a couple of questions about putting the dynamic pivot results to a local table or view. I think, I can use INTO statement to put the results of a temp table by the following addition, but I don't know how it serves to my purpose, since I need to put the results into a local table that I can select.
set @query = 'SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @selCols + ' INTO ##temp FROM
I've read some blogs, but couldn't figure out. There were some explanations that saying using a local table is not possible, and examples with Views are a little bit complicated. So, it would be great if you can help me in understanding this ?
Thanks in advance
If you are going to use a #temp table, do you really need a ##temp (Global) table? In most cases a single #temp (Local) is sufficient. You can then select x,x,x, from #temp.
The problem here is that you don't know how many columns are going to be returned, or their names. So How can you build a table with the correct structure.
The way I see it, you can
* Keep your existing structure and use the crosstab query to return the results that you are looking for.
* This feels really clunky, and id going to take some work ... Create a table with the known static columns and a whole lot of columns of various datatypes (like SharePoint does). You can then create a table with a mapping of which pivoted column goes into which field in your table.
Perhaps some of the more experienced guys might have other ideas.
October 20, 2016 at 2:00 am
seismicbeat (10/19/2016)
Hello,Sorry for bothering you again. I have a couple of questions about putting the dynamic pivot results to a local table or view. I think, I can use INTO statement to put the results of a temp table by the following addition, but I don't know how it serves to my purpose, since I need to put the results into a local table that I can select.
set @query = 'SELECT COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @selCols + ' INTO ##temp FROM
I've read some blogs, but couldn't figure out. There were some explanations that saying using a local table is not possible, and examples with Views are a little bit complicated. So, it would be great if you can help me in understanding this ?
Thanks in advance
Can your week numbers go beyond 53?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 20, 2016 at 3:11 am
ChrisM@Work (10/20/2016)
Can your week numbers go beyond 53?
Good catch Chris. I completely overlooked the fact that we are looking at weekly sales :doze:
Chris is on the right path. Create a table with your static columns, and a column for each week (1 to 53). Then modify your dynamic sql to insert/update that table directly.
Create your destination table.
CREATE TABLE dbo.DesiredResult (
COMPANY ...
, AREAS ...
, PRODUCT ...
, MARKET ...
, SUBTERRITORIES ...
, [1] INT NOT NULL DEFAULT(0)
, [2] INT NOT NULL DEFAULT(0)
, ...
, [52] INT NOT NULL DEFAULT(0)
, [53] INT NOT NULL DEFAULT(0)
);
Create 2 new variables for Insert/Udate
DECLARE
@queryUpdate AS NVARCHAR(MAX),
@queryInsert AS NVARCHAR(MAX);
Keep your existing @query as is.
Create the insert query, to insert NEW data. Note, you now exec the @queryInsert, and not the @query.
SET @queryInsert = 'INSERT INTO dbo.DesiredResult (COMPANY, AREAS, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ')
SELECT src.COMPANY, src.AREAS, src.PRODUCT, src.MARKET, ' + REPLACE(@cols, '[', 'src.[') + '
FROM ('
+ @query
+ ') as src
LEFT JOIN dbo.DesiredResult as dest
ON src.COMPANY = dest.COMPANY
AND src.AREAS = dest.AREAS
AND src.PRODUCT = dest.PRODUCT
AND src.MARKET = dest.MARKET
AND src.SUBTERRITORIES = dest.SUBTERRITORIES
WHERE dest.COMPANY IS NULL;';
PRINT (@queryInsert);
EXECUTE (@queryInsert);
I'll leave it to you to work on the update query. Depending on the data that you get, it might be easier to simply delete/truncate the destination table, then simply use the insert to add new data each time.
October 20, 2016 at 11:05 pm
Let's make this whole thing a bit more dynamic and let's make it so that we don't actually have to manually define a table with more than 50 columns in it. Let's also make it so that we can vary the output based on a few parameters.
First, let's build a few years of test data to demonstrate with...
--=====================================================================================================================
-- Create and populate the test table.
-- This is NOT a part of the solution. We're just creating a working test model here.
-- NOTE THAT THE SALES VALUES ARE FAIRLY RANDOM AND WON'T BE TOTALLY THE SAME FROM RUN TO RUN.
--=====================================================================================================================
--===== If the test table exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on the fly.
WITH cteGenAllTestData AS --45792 rows
( --=== Creates all Products, Areas, Districts, Years, and Months with a random Sales value.
SELECT *, Sales = ABS(CHECKSUM(NEWID()))%1001
FROM (SELECT Product FROM (VALUES ('A') ,('B') ,('C') ,('D') ,('E') ,('F') )v(Product) )p
CROSS JOIN (SELECT Area FROM (VALUES ('A1'),('B2'),('C3'),('D4'),('E5'),('F6'))v(Area) )a
CROSS JOIN (SELECT District FROM (VALUES ('AA'),('BB'),('CC'),('DD'),('EE'),('FF'))v(District) )d
CROSS JOIN (SELECT [Year] FROM (VALUES ('2013'),('2014'),('2015'),('2016') )v([Year]) )y
CROSS JOIN (SELECT TOP 53 [Week] = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)w
) --=== Creates and populates the test table with about 10% of the rows missing.
SELECT *
INTO #TestTable
FROM cteGenAllTestData
WHERE Sales >= 100 --Eliminates roughly 10% of the rows to test for missing element conversions to 0.
;
--===== Let's see what the test data looks like
SELECT *
FROM #TestTable
ORDER BY [Year], [Week], Product, Area, District
;
Now that we have some data to play with, let's play. 🙂 The following code takes 4 parameters for start year/week and end year/week and the code does all the rest including figuring out the number of columns to display according to the values of the parameters provided. Most of the details are (as always) in the code but, if you're not familiar with the WITH ROLLUP thing or what GROUP_ID does for you, you might want to look them up on Yabingooglehoo. They're powerful tools that allow you to do some pretty nifty things like producing sub-totals and grand totals. Try THAT with a PIVOT. :sick: Heh... it's almost like you don't need SSRS. :w00t:
I used the very same methods to do this as in the articles that Chris was kind enough to provide links for. I don't know what other folks call it but I also used a simple dynamic SQL technique that I call "tokenized dynamic SQL". It's NOT SQL INJECTION PROOF so be sure to check any gazintas if the parameters are character based and public facing. The parameters in the following aren't character based and neither are the substitution values from the table, in this case.
--=====================================================================================================================
-- Create the desired report using parameters to control the weeks displayed.
-- This could easily be turned into a stored procedure.
--=====================================================================================================================
--===== These could be the parameters for a stored procedure.
DECLARE @pYearStart INT = 2014
,@pWeekStart INT = 26
,@pYearEnd INT = 2015
,@pWeekEnd INT = 26
;
--===== Declare some obviously named local variables
DECLARE @SQL VARCHAR(MAX)
;
--===== If the labeling/preaggregation/report-prep table already exists, drop it to make reruns easier.
IF OBJECT_ID('tempdb..#ReportPrep','U') IS NOT NULL
DROP TABLE #ReportPrep
;
--===== Create and populate the reporting table using the data range prescribed by the given parameters
SELECT *
,WeekLabel = RIGHT([Year]+10000,4) + '-' + RIGHT([Week]+100,2)
INTO #ReportPrep
FROM #TestTable
WHERE [Year]*100+[Week] >= @pYearStart*100+@pWeekStart
AND [Year]*100+[Week] <= @pYearEnd *100+@pWeekEnd
;
--===== Create the static portion of the SELECT list.
SELECT @SQL = '
SELECT --If this is a Grand Total, Blank the column else display the Product
Product = CASE GROUPING_ID(Product,Area,District)
WHEN 7 THEN ""
ELSE Product
END
--If this is a Grand Total or Product Sub-Total, Blank the column else display the Area
,Area = CASE GROUPING_ID(Product,Area,District)
WHEN 7 THEN ""
WHEN 3 THEN ""
ELSE Area
END
--If this is any kind of a total, display the total type else display the District
,District = CASE GROUPING_ID(Product,Area,District)
WHEN 7 THEN "GRAND TOTAL"
WHEN 3 THEN "Product Sub-Total"
WHEN 1 THEN "Area Sub-Total"
WHEN 0 THEN District
END'
;
--===== Add the dynamic portion of the SELECT list.
SELECT TOP 2000000 @SQL += REPLACE(REPLACE(REPLACE('
,<<WeekLabel>> = SUM(CASE WHEN [Year] = <<Year>> AND [Week] = <<Week>> THEN Sales ELSE 0 END)'
--The other end of the REPLACEs
,'<<WeekLabel>>',QUOTENAME(WeekLabel))
,'<<Year>>' ,CAST([Year] AS CHAR(4)))
,'<<Week>>' ,CAST([Week] AS CHAR(2)))
FROM #ReportPrep
GROUP BY WeekLabel,[Year],[Week]
ORDER BY WeekLabel
;
--===== Add the static line total, the FROM clause, and the rest of the query.
SELECT @SQL = REPLACE(@SQL+'
,Total = SUM(Sales)
FROM #TestTable
GROUP BY Product,Area,District WITH ROLLUP
ORDER BY --Not really required unless someone adds something with an OVER clause in the SELECT list.
GROUPING(PRODUCT) ,Product
,GROUPING(Area) ,Area
,GROUPING(District),District
;'
,'"','''') --The end of the REPLACE for double quotes being changed to 2 single quotes
;
--===== Execute the dynamic CROSSTAB
EXEC (@SQL)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2016 at 12:13 am
@desnorton, @ChrisM@Work and @jeff Moden, I really appreciate to all your helps, I can't even describe how I'm grateful for what you've done. You did help me out everytime I got stuck and explained the things in great detail. I'm going to try and work on what you elaborated above to pull this off. 🙂
Thank you so much again.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply