May 9, 2009 at 1:43 am
Hello,
I would like to create a query from a single table with the following columns.
SEQNO is a unique key
Name ID Amount Date JOBID SEQNO
Mark 9 200 1/2/09 1001 1
Peter 3 300 1/2/09 1001 2
Steve 1 200 2/2/09 1001 3
Mark 9 200 3/2/09 1001 1
Peter 3 300 4/2/09 1001 2
Steve 1 200 5/2/09 1001 3
Hally 1 200 5/2/09 1002 3
The query should output in this format by SUBJOBID :-
NAME ID 1/2 2/2 3/2 4/2 5/2 JOBID
Mark 9 200 NULL 200 NULL NULL 1001
Peter 3 300 NULL NULL 300 NULL 1001
Steve 1 NULL 200 NULL NULL 200 1001
I have been going over pivot queries for this. But I don't seem to get anywhere. Could some one help ?
May 9, 2009 at 3:43 am
Hi martin
Try this;
DECLARE @t TABLE (Name VARCHAR(40), Id INT, Amount FLOAT, Date DATETIME, JobId INT, SeqNo INT)
INSERT INTO @t
SELECT 'Mark', '9', '200', '1/2/09', '1001', '1'
UNION ALL SELECT 'Peter', '3', '300', '1/2/09', '1001', '2'
UNION ALL SELECT 'Steve', '1', '200', '2/2/09', '1001', '3'
UNION ALL SELECT 'Mark', '9', '200', '3/2/09', '1001', '1'
UNION ALL SELECT 'Peter', '3', '300', '4/2/09', '1001', '2'
UNION ALL SELECT 'Steve', '1', '200', '5/2/09', '1001', '3'
UNION ALL SELECT 'Hally', '1', '200', '5/2/09', '1002', '3'
SELECT
Name,
Id,
[1/2/09],
[2/2/09],
[3/2/09],
[4/2/09],
[5/2/09],
JobId
FROM @t
PIVOT
(
MIN(Amount)
FOR Date IN ([1/2/09], [2/2/09], [3/2/09], [4/2/09], [5/2/09])
) AS pvt
WHERE JobId = 1001
Greets
Flo
May 9, 2009 at 9:19 am
I have been working on a very similar problem, and was about to post when I noticed martin's problem was virtually the same,
I noticed Flo has hard-coded the dates in the solution as column names. My question is - Is there any way to do this kind of pivot with a dynamic date range? - just by supplying a startdate and enddate as parameters to the query
I was working on dynamic SQL to create the PIVOT statement - but it occurred to me that there may be some other way.
May 9, 2009 at 9:50 am
Hi Tom
I think there is no other way than dynamic SQL to create a dynamic pivot in SQL. This depends on the fact that the PIVOT is usually a reporting tool feature. Excel is able to create new columns for each new date (in above case) because it is made for this. In my book SSE is not the best analyzes tool ;-). For more complex requirements I would suggest SSAS.
Maybe you can encapsulate the dynamic SQL part into a procedure.
Greets
Flo
May 9, 2009 at 11:14 am
Hi Flo,
It was quite simple to get the dynamic version working - so I didn't need to venture into SSAS or Excel.
CREATE TABLE #TMP1 (
ADate datetime,
Amount float,
Name varchar(30) );
INSERT INTO #TMP1 (ADate, Amount, Name)
SELECT '2009-05-01', 1, 'A' UNION
SELECT '2009-05-01', 2, 'B' UNION
SELECT '2009-05-01', 2, 'C' UNION
SELECT '2009-05-03', 2, 'A' UNION
SELECT '2009-05-03', 4, 'D' UNION
SELECT '2009-05-04', 7, 'A' UNION
SELECT '2009-05-04', 5, 'E' UNION
SELECT '2009-05-04', 8, 'F' UNION
SELECT '2009-05-07', 4, 'B' UNION
SELECT '2009-05-07', 3, 'C' UNION
SELECT '2009-05-08', 2, 'A' UNION
SELECT '2009-05-08', 2, 'B';
DECLARE @sql nvarchar(max);
DECLARE @DATELIST nvarchar(4000);
SET @DATELIST = ''
SELECT @DATELIST = CASE @DATELIST
WHEN '' THEN '[' + Convert(nvarchar(11),Dates.ADate,109) + ']'
ELSE @DATELIST + ', [' + Convert(nvarchar(11),Dates.ADate,109) + '] '
END
FROM ( SELECT DISTINCT ADate FROM #TMP1 ) Dates
ORDER BY Dates.ADate;
SET @sql = 'SELECT Name, ' + @DATELIST +
'FROM #TMP1 ' +
'PIVOT ( MIN(Amount) ' +
'FOR ADate IN ( ' + @DATELIST + ') ' +
') AS PVT;'
EXEC (@SQL);
I should probably put a limiter on the number of date columns in case someone queries a huge date range.
May 9, 2009 at 11:25 am
Hi Tom
Sure it is not a big deal to create a dynamic SQL for a PIVOT function I thought you are asking for a build-in function which supports dynamic PIVOTS. Seems I misunderstood you.
Nice approach!
Greets
Flo
May 9, 2009 at 2:48 pm
Hi Flo,
No misunderstanding, I was looking for a built-in approach, but have learned that T-SQL doesn't have one, so I finished my dynamic SQL. It seemed natural to post my solution.
Thanks for your comments.
May 11, 2009 at 9:37 am
Thanks Tom,
It quite a complex query you've got there, and I was hoping you would explain it. I would like to add subjobid and a date range as parameters. Where would I have this ?
Kind Regards,
May 11, 2009 at 11:50 am
Martin, you may find this a little easier to understand. It is what some people call a crosstab approach, although it produces the same result in your case as a pivot. Basically, it generates a case expression for each date that has a value.
declare @dynSQL nvarchar(4000) ,@jobID int ,@start date, @end date
--
set @dynSQL = 'select name,id,jobid'+CHAR(10) -- first line of query to be executed
set @jobID = 1001
set @start = '2/2/2009'
set @end = '4/2/2009'
--
create table #sample (Name char(10), ID int, Amount int, xDate datetime, JOBID int, SEQNO int)
insert into #sample
select 'Mark', 9, 200, '1/2/09', 1001, 1 union all
select 'Peter', 3, 300, '1/2/09', 1001, 2 union all
select 'Steve', 1, 200, '2/2/09', 1001, 3 union all
select 'Mark', 9, 200, '3/2/09', 1001, 1 union all
select 'Peter', 3, 300, '4/2/09', 1001, 2 union all
select 'Steve', 1, 200, '5/2/09', 1001, 3 union all
select 'Hally', 1, 200, '5/2/09', 1002, 3
--
;with cte1 as (select *
from #sample
where 1 = 1
and jobID = @jobID
and xdate between @start and @end
)
--
,cte2 as (select distinct cast(xdate as DATE)as xdate from cte1)
--
-- this adds a line for each date column that has a value with the right jobid in the date range
select @dynSQL = @dynSQL+',max(case when xdate = ' + quotename(xdate,'''') + ' then amount else null end) as ' + quotename(xdate)+CHAR(10)
from cte2
order by xdate
--
-- this adds the where, group by, and order by clauses to the query to be executed
set @dynSQL = @dynSQL + 'from #sample'+CHAR(10)
+ 'where 1 = 1 '+char(10)
+ ' and jobID = ' + quotename(cast(@jobID as varchar(10)),'''')+char(10)
+ ' and xdate between '+ quotename(@start,'''')+' and '+ quotename(@end,'''')+char(10)
+ 'group by jobID, name, id'+char(10)
+ 'order by jobID, name, id'+char(10)
--
select @dynSQL
exec sp_executeSQL @dynSQL
--
drop table #sample
--
The above example only shows columns for dates where actual amounts appear in the result sets. If you wanted every date in the range to show regardless of whether or not there were any amounts for it, you could just take the where clause out of cte1 and generate a list of dates in cte2 using a tally table or some other method and the dynamic SQL code should still work. In fact, it should run faster in production because it would not have to scan the #sample table to build the dynamic SQL string.
Please let us know if you have any questions.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 12, 2009 at 3:03 am
Hi martin
This is quite a long post, as the code is in twice - first the complete code: - its broken down after, clause by clause. I hope I haven't pitched it too simple or too complex for your understanding.
CREATE TABLE #TMP1 (
ADate datetime,
Amount float,
Name varchar(30),
subjId INT,
objId INT IDENTITY(1,1) );
INSERT INTO #TMP1 (ADate, Amount, Name, subjId)
SELECT '2009-05-01', 1, 'A', 10 UNION
SELECT '2009-05-01', 2, 'B', 12 UNION
SELECT '2009-05-01', 2, 'C', 10 UNION
SELECT '2009-05-03', 2, 'A', 11 UNION
SELECT '2009-05-03', 4, 'D', 10 UNION
SELECT '2009-05-04', 7, 'A', 12 UNION
SELECT '2009-05-04', 5, 'E', 12 UNION
SELECT '2009-05-04', 8, 'F', 11 UNION
SELECT '2009-05-07', 4, 'B', 12 UNION
SELECT '2009-05-07', 3, 'C', 10 UNION
SELECT '2009-05-08', 2, 'A', 11 UNION
SELECT '2009-05-08', 2, 'B', 10;
-- Parameters
DECLARE @StartDate Datetime;
DECLARE @EndDate Datetime;
DECLARE @SubjID INT;
-- Parameter values
SET @StartDate = '2009-05-03';
SET @EndDate = '2009-05-07';
SET @SubjID = 11;
DECLARE @sql nvarchar(max);
DECLARE @DATELIST nvarchar(4000);
SET @DATELIST = ''
SELECT @DATELIST = CASE @DATELIST
WHEN '' THEN '[' + Convert(nvarchar(11),MyDates.ADate,109) + ']'
ELSE @DATELIST + ', [' + Convert(nvarchar(11),MyDates.ADate,109) + '] '
END
FROM ( SELECT DISTINCT ADate FROM #TMP1
WHERE SubjID = @SubjID
AND ADate BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.ADate;
SET @sql = 'WITH ParamQry AS ( ' +
' SELECT ADate, Amount, Name, SubjID, objID ' +
' FROM #TMP1 ' +
' WHERE SubjID = ' + convert(varchar(8),@SubjID) +
' AND ADate BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +
' ) ' +
'SELECT Name, objID, ' + @DATELIST +
'FROM ParamQry ' +
'PIVOT ( MIN(Amount) ' +
'FOR ADate IN ( ' + @DATELIST + ') ' +
') AS PVT;'
PRINT @sql;
EXEC (@SQL);
I've added subjID and objID columns to the temp table. SubjID is an arbitrary number, and is only used to filter the data. objId is a unique id on each record.
Next I've declared filtering parameters on subjID and date range
-- Parameters
DECLARE @StartDate Datetime;
DECLARE @EndDate Datetime;
DECLARE @SubjID INT;
-- Parameter values
SET @StartDate = '2009-05-03';
SET @EndDate = '2009-05-07';
SET @SubjID = 11;
The next part is a shortcut to get a list of unique dates, and only the dates which are in range, and will contain data. If you want the entire date range, i.e. every date in the range, not just the dates that contain data - see the Tally table articles on this site for how to generate a list of sequential dates. The convert function is used to get the date in text format, and because these dates will be column names, they are [enclosed] in square brackets
SET @DATELIST = ''
SELECT @DATELIST = CASE @DATELIST
WHEN '' THEN '[' + Convert(nvarchar(11),MyDates.ADate,109) + ']'
ELSE @DATELIST + ', [' + Convert(nvarchar(11),MyDates.ADate,109) + '] '
END
I only want each date to appear once, so a subquery is necessary in order to apply DISTINCT to the dates. The subquery needs a name (MyDates) - but you don't need to reference the columns using MyDates.ADate. I only did this for clarity. Because a subquery is already in use I have filtered the data required when creating the @DATELIST, by including the filter in the subquery.
FROM ( SELECT DISTINCT ADate FROM #TMP1
WHERE SubjID = @SubjID
AND ADate BETWEEN @StartDate AND @EndDate ) AS MyDates
ORDER BY MyDates.ADate;
Next I want to dynamically create the Pivot SQL, but filter the data before submitting it to the PIVOT So I've added a CTE - Common Table Expression (WITH) - and named it ParamQry to supply data to the PIVOT.
The dates and numbers must be converted to varchar, to be concatenated into to the @sql variable.
SET @sql = 'WITH ParamQry AS ( ' +
' SELECT ADate, Amount, Name, SubjID, objID ' +
' FROM #TMP1 ' +
' WHERE SubjID = ' + convert(varchar(8),@SubjID) +
' AND ADate BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +
' ) ' +
And assemble the PIVOT part of the statement using the ParamQry CTE output. I've added the objID to the select list. Note PIVOT requires an aggregate, you can use MIN, MAX, SUM, COUNT etc - I've used MIN because I'm certain there is only one Amount value per Name/ObjID/Date combination, so nothing will be excluded. I could have used MAX - it should give the same result.
'SELECT Name, objID, ' + @DATELIST +
'FROM ParamQry ' +
'PIVOT ( MIN(Amount) ' +
'FOR ADate IN ( ' + @DATELIST + ') ' +
') AS PVT;'
optionally print out the dynamic statement
PRINT @sql;
Here is an example of what it will print
WITH ParamQry AS ( SELECT ADate, Amount, Name, SubjID, objID FROM #TMP1 WHERE SubjID = 11 AND ADate BETWEEN '2009-05-03' AND '2009-05-07' ) SELECT Name, objID, [May 3 2009], [May 4 2009] FROM ParamQry PIVOT ( MIN(Amount) FOR ADate IN ( [May 3 2009], [May 4 2009] ) ) AS PVT;
and execute it
EXEC (@SQL);
And one more point, with my data (several thousand records) the query was taking far too long, when I looked at the execution plan - and most of the processing was in a table scan and sort on the dates - so I put an index on the date column - equivalent to my example above would be something like.
CREATE INDEX IDX_TMP_Dates ON #Tmp1(ADate);
I hope this is clear to you - any questions just ask.
May 14, 2009 at 3:00 am
Thanks Bob,
However when I two dates with the same entries e.g.
NAME ID AMT DATE JOBID
Mark 9 200 02/01/2009 1001
Mark 9 400 02/01/2009 1001
I need to add the two amounts together which will display under the same date.
name id Amount
mark 9 600
Where should I tweak to display this ?
May 14, 2009 at 4:52 am
For Bob's version, I guess you'd want to pre-sum the data before it gets to the dynamic query
SELECT SUM(Amount) AS Amount, Name, ID, JobID, Date
INTO #Sample2
FROM #Sample
GROUP BY Name, ID, JobID, Date
Then select from #Sample2 in the final line setting @dynSQL
I can't see another way of doing it because amount is actually selected in this part of his query - I couldn't see a way of getting a SUM function in there without getting very of complex.
select @dynSQL = @dynSQL+',max(case when xdate = ' + quotename(xdate,'''') + ' then amount else null end) as ' + quotename(xdate)+CHAR(10)
from cte2
My data is structured differently, as I shouldn't have duplicate/multiple amounts for a given Name/Date - but to take care of that possibility do the following
1) Apply UNION ALL (instead of UNION) in creating the test Data (- to make sure any duplicates do get into the temporary table
2) Exclude the objID from the query (in 2 places) - because this is unique - and will serve to separate data we want summed, and (3) use Sum(Amount) in the PIVOT instead of using MIN(Amount) - by changing the dynamic SQL to this:
SET @sql = 'WITH ParamQry AS ( ' +
' SELECT ADate, Amount, Name, SubjID ' +
' FROM #TMP1 ' +
' WHERE SubjID = ' + convert(varchar(8),@SubjID) +
' AND ADate BETWEEN ''' + Convert(varchar(10),@StartDate,120) + ''' AND ''' + Convert(varchar(10),@EndDate,120) + ''' ' +
' ) ' +
'SELECT Name, ' + @DATELIST +
'FROM ParamQry ' +
'PIVOT ( SUM(Amount) ' +
'FOR ADate IN ( ' + @DATELIST + ') ' +
') AS PVT;'
May 14, 2009 at 6:28 am
Thanks Tom,
That was quick. I managed to get the answer, here is what I did
I changed the MIN to sum.
select @dynSQL = @dynSQL+',sum(case when TRANSDATE = ' + quotename(TRANSDATE,'''') + ' then Amount else null end) as ' + quotename(TRANSDATE)+CHAR(10)
At then end of this values I would like to sum up all the amounts for the rows at the last column called total. How do I add these amounts ?
May 14, 2009 at 6:57 am
I tried what you did before and got loads of errors - must have got a typo somewhere. :blush:
For totals - do you mean a separate total line at the end of the report?
For that (in T-SQL) you'll need a separate query - summing the results of the main report.
Or a reporting app will do this for you, once you've got the main query done.
If its a one-off - then you can select your main output into a temporary table then get your grand total lines off that. If your likely to be doing a lot of this, then a bit of investigation of SQL Server Reporting Services could be an option.
May 14, 2009 at 8:27 am
I have now tried both queries. They work wonderfully. The totals line at the end of the report is also a bit important.
How will the T-SQL relate to the main query ? I need an extra column at the end , which will sum up all the amounts for a particular row.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply