November 25, 2016 at 1:00 pm
halifaxdal (11/25/2016)
Thank you very much for the revised script which is amazing, the result returns lots of NULL for WeekNo fields, how can the script be modified to get that?I also need to modify the WeekNo so it is generated in the actual order, here is my script but not working:
SELECT DISTINCT WeekNo FROM Staging WHERE WeekNo <> ''ORDER BY CASE
WHEN SUBSTRING(WeekNo, 1,4) = 'Jan' THEN 1
WHEN SUBSTRING(WeekNo, 1,4) = 'Feb' THEN 2
WHEN SUBSTRING(WeekNo, 1,4) = 'Mar' THEN 3
WHEN SUBSTRING(WeekNo, 1,4) = 'Apr' THEN 4
WHEN SUBSTRING(WeekNo, 1,4) = 'May' THEN 5
WHEN SUBSTRING(WeekNo, 1,4) = 'Jun' THEN 6
WHEN SUBSTRING(WeekNo, 1,4) = 'Jul' THEN 7
WHEN SUBSTRING(WeekNo, 1,4) = 'Aug' THEN 8
WHEN SUBSTRING(WeekNo, 1,4) = 'Sep' THEN 9
WHEN SUBSTRING(WeekNo, 1,4) = 'Oct' THEN 10
WHEN SUBSTRING(WeekNo, 1,4) = 'Nov' THEN 11
WHEN SUBSTRING(WeekNo, 1,4) = 'Dec' THEN 12
END
Error:
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Do you have a fix?
Thanks.
You are overthinking the problem with the order. It was already in order before...alphabetically. The problem is you are not storing it as a date (which you should) but a small modification will get you what you need.
DECLARE @sql VARCHAR(MAX)
;WITH cte AS
(
SELECT DISTINCT CAST(WeekNo AS DATE) AS WeekNo FROM Staging WHERE WeekNo <> ''
)
SELECT @sql =
'SELECT [ProjectWork Number] ' +
(SELECT ' , MAX(CASE WHEN CAST(WeekNo AS DATE) = ' + '''' + CAST( WeekNo AS VARCHAR(12)) + '''' + ' THEN WeekValue END) AS ' + '''' + CAST( WeekNo AS VARCHAR(12)) + ''''
FROM cte ORDER BY WeekNo FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)') +
' FROM Staging' +
' GROUP BY [ProjectWork Number]'
EXECUTE (@sql)
AS for the NULL's well again that is to be expected. If you have no data for a project work number for that 'week' then you'll get a NULL. I don't know what your real data looks like but there are so many ways to slice and dice this. You can limit which weeks you show or only show certain project numbers. This can all be done with just small changes.
November 25, 2016 at 5:51 pm
Thank you very much.
Your script use MAX instead of SUM, is it a typo or is there any consideration?
New testing result:
1. The new script (MAX or SUM) returns incorrect stats for unknown reason (for Nov 1, 2015 it sums up to a smaller number than the number generated with the previous pivot solution)
2. Why there are still "some" weekno are NULL, and some are 0? Can all the NULL be converted to 0 in the result within the query?
3. How can I get a monthly SUM with the script? is that possible?
Thank you again for your kind help, it is really appreciated,
November 28, 2016 at 7:32 am
halifaxdal (11/25/2016)
Thank you very much.Your script use MAX instead of SUM, is it a typo or is there any consideration?
New testing result:
1. The new script (MAX or SUM) returns incorrect stats for unknown reason (for Nov 1, 2015 it sums up to a smaller number than the number generated with the previous pivot solution)
2. Why there are still "some" weekno are NULL, and some are 0? Can all the NULL be converted to 0 in the result within the query?
3. How can I get a monthly SUM with the script? is that possible?
Thank you again for your kind help, it is really appreciated,
1. Yes, you can use SUM. Crosstabs require an aggregate function to work. I used MAX because you only had one value per weekno, you can use SUM where it is appropriate.
2. The NULL's are to be expected with such sparse data. If you want to display a zero instead you just need to add an ELSE to the dynamic CASE statement.
i.e. THEN WeekValue ELSE 0 END
3. Is it possible? Probably but I'm hesitant to give more solutions you won't be able to maintain. I'd recommend you get a feel for the code you have now and get a good understanding of how it works.
Cheers,
November 28, 2016 at 9:50 am
Y.B. (11/28/2016)
halifaxdal (11/25/2016)
Thank you very much.3. Is it possible? Probably but I'm hesitant to give more solutions you won't be able to maintain. I'd recommend you get a feel for the code you have now and get a good understanding of how it works.
Cheers,
Thank you YB but please do not go away, this is the first time I am working on Pivot table in your way, I badly need the result be grouped into [ProjectWork Name] and sum up monthly stats.
While I am continue doing my own research, it would be appreciated if you can make the script truly working as per needed.
December 1, 2016 at 1:50 pm
halifaxdal (11/28/2016)
Y.B. (11/28/2016)
halifaxdal (11/25/2016)
Thank you very much.3. Is it possible? Probably but I'm hesitant to give more solutions you won't be able to maintain. I'd recommend you get a feel for the code you have now and get a good understanding of how it works.
Cheers,
Thank you YB but please do not go away, this is the first time I am working on Pivot table in your way, I badly need the result be grouped into [ProjectWork Name] and sum up monthly stats.
While I am continue doing my own research, it would be appreciated if you can make the script truly working as per needed.
How's this work for you? (I included the test table creation as well, so that others can test and make use of it):
CREATE TABLE [dbo].[Staging](
[ProjectWork Number] [nvarchar](255) NULL,
[WeekNo] [nvarchar](12) NULL,
[WeekValue] [float] NULL
);
INSERT INTO dbo.Staging ([ProjectWork Number], WeekNo, WeekValue)
VALUES
('12345', 'Dec 06, 2015', 12.3),
('12345', 'Dec 13, 2015', 12.3),
('12345', 'Dec 20, 2015', 12.3),
('12345', 'Dec 27, 2015', 12.3),
('12345', 'Jan 03, 2016', 12.3),
('RXAXVX', 'Jan 10, 2016', 45.6),
('3210', 'Jan 17, 2016', 45.6),
('3210', 'Jan 24, 2016', 45.6),
('3210', 'Jan 31, 2016', 45.6),
('3210', 'Nov 01, 2015', 45.6),
('3210', 'Nov 08, 2015', 45.6),
('3210', 'Nov 15, 2015', 78.9),
('3210', 'Nov 22, 2015', 78.9),
('34567', 'Nov 29, 2015', 78.9),
('34567', 'Apr 03, 2016', 78.9),
('34567', 'Apr 10, 2016', 78.9),
('34567', 'Apr 17, 2016', 78.9),
('34567', 'Apr 24, 2016', 0.12),
('34567', 'Feb 07, 2016', 0.12),
('34567', 'Feb 14, 2016', 0.12),
('ABCED', 'Feb 21, 2016', 0.12),
('ABCED', 'Feb 28, 2016', 0.12),
('ABCED', 'Jan 31, 2016', 0.12),
('ABCED', 'Mar 06, 2016', 3.8),
('ABCED', 'Mar 13, 2016', 3.8),
('ABCED', 'Mar 20, 2016', 3.8),
('RXAXVX', 'Mar 27, 2016', 3.8),
('RXAXVX', 'Jul 03, 2016', 0.135),
('RXAXVX', 'Jul 10, 2016', 0.135),
('RXAXVX', 'Jul 17, 2016', 0.135),
('RXAXVX', 'Jul 24, 2016', 0.135),
('RXAXVX', 'Jul 31, 2016', 0.135),
('RXAXVX', 'Jun 05, 2016', 333),
('EEEEE', 'Jun 12, 2016', 333),
('EEEEE', 'Jun 19, 2016', 333),
('EEEEE', 'Jun 26, 2016', 333),
('EEEEE', 'May 01, 2016', 333),
('EEEEE', 'May 08, 2016', 12.3),
('EEEEE', 'May 15, 2016', 12.3),
('BBB', 'May 22, 2016', 20.9),
('BBB', 'May 29, 2016', 20.9),
('BBB', 'Aug 07, 2016', 20.9),
('BBB', 'Aug 14, 2016', 20.9),
('BBB', 'Aug 21, 2016', 20.9),
('BBB', 'Aug 28, 2016', 20.9),
('BBB', 'Jul 31, 2016', 20.9),
('CACACA', 'Oct 02, 2016', 12.3),
('CACACA', 'Oct 09, 2016', 12.3),
('CACACA', 'Oct 16, 2016', 12.3),
('CACACA', 'Oct 23, 2016', 12.3),
('CACACA', 'Oct 30, 2016', 12.3);
DECLARE @sql AS VARCHAR(MAX);
WITH cte AS (
SELECT DISTINCT CAST(WeekNo AS date) AS WeekNo
FROM dbo.Staging
WHERE WeekNo <> ''
)
SELECT @sql =
'SELECT [ProjectWork Number]' + CHAR(13) + CHAR(10) +
(
SELECT ' , ISNULL(MAX(CASE WHEN CAST(WeekNo AS DATE) = ' + '''' + CAST( WeekNo AS VARCHAR(12)) + '''' +
' THEN WeekValue END), 0) AS ' + '''' + CAST( WeekNo AS VARCHAR(12)) + '''' + CHAR(13) + CHAR(10)
FROM cte
ORDER BY WeekNo
FOR XML PATH(''), TYPE
).value('./text()[1]', 'nvarchar(MAX)') +
' FROM Staging' + CHAR(13) + CHAR(10) +
' GROUP BY [ProjectWork Number]';
PRINT @sql;
EXECUTE (@sql);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 1, 2016 at 5:37 pm
Thank you Steve, the work was submitted already but yes your help would be seen by later visitor.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply