Need help on a query, not sure if PIVOT is what I need to use

Question

Post reply

Need help on a query, not sure if PIVOT is what I need to use

woody_rd

Ten Centuries

Points: 1012

April 18, 2019 at 4:52 pm

#3454225

I have a need to get output in the following format:

In this case, I have an importer (importer number field), a frequency for importer validation (import freq), an name of the last person who validated the importer (importer name field), and finally the last date the importer was validated or audited.

In a subsequent table joined by a uniqueidentifier field, I have a table of units associated with the importer and the date of import. I want to be able to list the 4 main fields along with how many units were imported on a current month, rolling basis.

It seems like a PIVOT is what I need to do, but I'm not at all familiar with them and have not been able to get the query to work as yet. And since the dates will change daily as we move to the next day, i'm not sure how to handle that either.

Any insight or assistance that you can offer is appreciated.

Here is a sample dataset that I've been using in my testing:

create table #temp_Imports (
ImportId uniqueidentifier not null,
ImporterNumber nvarchar(100) not null,
ImportFreq int null,
ImporterName nvarchar(100) null,
ImportDate datetime null)

INSERT INTO #temp_Imports VALUES ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','12345test',30,'tester1','2019-04-02')
INSERT INTO #temp_Imports VALUES (NEWID(),'23456test',45,'tester1','2019-04-05')
INSERT INTO #temp_Imports VALUES (NEWID(),'09876test',30,'tester1','2019-04-04')
INSERT INTO #temp_Imports VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','56432test',45,'tester1','2019-04-11')
INSERT INTO #temp_Imports VALUES (NEWID(),'83256test',30,'tester1','2019-04-10')
INSERT INTO #temp_Imports VALUES ('03E8E500-E9FE-4116-89BB-5FE63863A758','08743test',60,'tester2','2019-04-09')
INSERT INTO #temp_Imports VALUES (NEWID(),'34567test',90,'tester2','2019-04-07')
INSERT INTO #temp_Imports VALUES ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','01010test',90,'tester2','2019-04-02')
INSERT INTO #temp_Imports VALUES (NEWID(),'98989test',30,'tester2','2019-04-16')
INSERT INTO #temp_Imports VALUES ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','76567test',60,'tester2','2019-04-15')

create table #temp_ImportUnits (
ImportId uniqueidentifier not null,
UnitImportDate datetime null )

INSERT INTO #temp_ImportUnits VALUES ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-01')
INSERT INTO #temp_ImportUnits VALUES ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-02')
INSERT INTO #temp_ImportUnits VALUES ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-09')
INSERT INTO #temp_ImportUnits VALUES ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-11')
INSERT INTO #temp_ImportUnits VALUES ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-16')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-01')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-02')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-04')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-11')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-12')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-12')
INSERT INTO #temp_ImportUnits VALUES ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-12')
INSERT INTO #temp_ImportUnits VALUES ('03E8E500-E9FE-4116-89BB-5FE63863A758','2019-04-08')
INSERT INTO #temp_ImportUnits VALUES ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','2019-04-04')
INSERT INTO #temp_ImportUnits VALUES ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','2019-04-01')
INSERT INTO #temp_ImportUnits VALUES ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','2019-04-10')
INSERT INTO #temp_ImportUnits VALUES ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-11')
INSERT INTO #temp_ImportUnits VALUES ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-10')
INSERT INTO #temp_ImportUnits VALUES ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-08')
INSERT INTO #temp_ImportUnits VALUES ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-12')
INSERT INTO #temp_ImportUnits VALUES ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-13')

select * from #temp_Imports
select * from #temp_ImportUnits

drop table #temp_Imports
drop table #temp_ImportUnits

Regards,

Woody

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 1

I would normally do this job by creating a temp table, then dynamically adding and populating columns, then select the output. The advantage of that is that I can account for the NULL values and turn those into "0" values when needed. However, I have shorter dynamic SQL version, but it has two drawbacks. First, rows that are not imported will have NULL instead of 0, and the ImportID appears at the end of the list. Let me know if those are showstoppers, and I can post the alternate solution.

DECLARE @StartDate DATE = '20190401',

@PivotSQL VARCHAR(MAX) = 'SELECT ',

@FullSQL VARCHAR(MAX);

WITH LocalTally

AS (SELECT TOP 35 ROW_NUMBER() OVER (ORDER BY name) AS N FROM sys.columns),

BaseData

AS (

SELECT N, @StartDate AS [StartDate], DATEADD(DAY, N - 1, @StartDate) AS ImportDate,

CONVERT(VARCHAR(10), DATEADD(DAY, N - 1, @StartDate), 112) AS ImportDateText

FROM LocalTally

WHERE DATEADD(DAY, N - 1, @StartDate) < DATEADD(MONTH, 1, @StartDate)

)

SELECT @PivotSQL = @PivotSQL + 'SUM(CASE WHEN UnitImportDate = ''' + ImportDateText + ''' THEN 1 ELSE 0 END) AS [' + CONVERT(VARCHAR(10), ImportDate, 121) + '],'

FROM BaseData

SET @PivotSQL = @PivotSQL + ' ImportId FROM #temp_ImportUnits GROUP BY ImportId '

SET @FullSQL =

'SELECT i.ImporterNumber,

i.ImportFreq,

i.ImporterName,

i.ImportDate,

u.*

FROM #temp_Imports i

LEFT JOIN (' + @PivotSQL + ') u

ON i.ImportID = u.ImportID

ORDER BY i.ImporterNumber

'

EXECUTE (@FullSQL)

Sorry, I still can't figure out how to post good code blocks on this new site.

woody_rd Ten Centuries Points: 1012 More actions · Answer 2

Thanks for this! I'm not sure what I'm doing wrong, but it's not liking the dates as columns. They're all producing errors as such:

Msg 207, Level 16, State 1, Line 52

Invalid column name '20190401'.

Msg 207, Level 16, State 1, Line 52

Invalid column name '20190402'.

etc.

Syntax looks accurate as I parse through it. Not sure why it's not liking that text as a column name. Any thoughts?

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

I see that the formatter changed two single quotes in the text to double quotes. The line that says, "SUM(CASE WHEN UnitImportDate..." should have all single quotes, not double and single quotes mixed. Try changing the double quotes to two single quotes and see if that makes a difference. If you PRINT the @PivotSQL and try to run it, you should see what is happening.

The idea is that the final SQL needs to have the dates quoted (in single quotes). To accomplish that, we have to place two single quotes in the string. To SELECT a single quote, I have to type four of them in a row, "SELECT ' ' ' ' " (spaces added so the formatter doesn't hack it). The first and last delimit the string, and the single quote I want is escaped by typing two of them.

woody_rd Ten Centuries Points: 1012 More actions · Answer 4

Ah, yes... that did it. Sorry, I should have caught that! Many thanks for the assistance!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is my take:

CREATE TABLE [#temp_Imports]
(
  [ImportId]       uniqueidentifier NOT NULL
, [ImporterNumber] nvarchar(100)    NOT NULL
, [ImportFreq]     int              NULL
, [ImporterName]   nvarchar(100)    NULL
, [ImportDate]     datetime         NULL
); -- You should always terminate your SQL statements with a semicolon.

INSERT INTO #temp_Imports(
    [ImportId]      
  , [ImporterNumber]
  , [ImportFreq]    
  , [ImporterName]  
  , [ImportDate]    
)
VALUES
    ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','12345test',30,'tester1','2019-04-02')
  , (NEWID(),'23456test',45,'tester1','2019-04-05')
  , (NEWID(),'09876test',30,'tester1','2019-04-04')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','56432test',45,'tester1','2019-04-11')
  , (NEWID(),'83256test',30,'tester1','2019-04-10')
  , ('03E8E500-E9FE-4116-89BB-5FE63863A758','08743test',60,'tester2','2019-04-09')
  , (NEWID(),'34567test',90,'tester2','2019-04-07')
  , ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','01010test',90,'tester2','2019-04-02')
  , (NEWID(),'98989test',30,'tester2','2019-04-16')
  , ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','76567test',60,'tester2','2019-04-15'); -- Using MS SQL 2016, use table value constructors

CREATE TABLE [#temp_ImportUnits]
(
  [ImportId]       uniqueidentifier NOT NULL
, [UnitImportDate] datetime         NULL
);

INSERT INTO #temp_ImportUnits(
    [ImportId]      
  , [UnitImportDate]
)
VALUES
    ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-01')
  , ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-02')
  , ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-09')
  , ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-11')
  , ('61C2C0FF-30D3-42F0-956B-1A06BF7DDA91','2019-04-16')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-01')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-02')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-04')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-11')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-12')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-12')
  , ('E6949BA7-F431-493E-A422-11290C98BA7F','2019-04-12')
  , ('03E8E500-E9FE-4116-89BB-5FE63863A758','2019-04-08')
  , ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','2019-04-04')
  , ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','2019-04-01')
  , ('524018A9-6393-4BBB-A208-D2B0DBBF6A1F','2019-04-10')
  , ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-11')
  , ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-10')
  , ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-08')
  , ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-12')
  , ('493F35F7-31A1-4E03-A692-A48F2EACFFA3','2019-04-13');

SELECT * from #temp_Imports;
select * from #temp_ImportUnits;

DECLARE @DateValues nvarchar(MAX)
       ,@SQLCmd nvarchar(MAX);

DECLARE @SQLCmdTemplate nvarchar(MAX) = N'
SELECT
    [ti].[ImportId]      
  , [ti].[ImporterNumber]
  , [ti].[ImportFreq]    
  , [ti].[ImporterName]  
  , [ti].[ImportDate]    
~DateValues~
FROM
  [#temp_Imports] as [ti]
  INNER JOIN [#temp_ImportUnits] AS [tiu]
    ON [ti].[ImportId] = [tiu].[ImportId]
GROUP BY
    [ti].[ImportId]      
  , [ti].[ImporterNumber]
  , [ti].[ImportFreq]    
  , [ti].[ImporterName]  
  , [ti].[ImportDate];    
';

WITH MinMaxDates AS (
SELECT
  MinDate = MIN([tiu].[UnitImportDate])
, MaxDate = MAX([tiu].[UnitImportDate])
FROM
  [#temp_ImportUnits] AS [tiu]
), eTally AS (
SELECT TOP(SELECT DATEDIFF(DAY,MinDate,MaxDate) + 1 FROM MinMaxDates)
  n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM
  sys.all_columns a
  CROSS JOIN sys.all_columns b
)
SELECT @DateValues = STUFF((SELECT  NCHAR(13) + NCHAR(10) + N'  , ' + QUOTENAME(CONVERT(char(10), CAST(DATEADD(DAY,[n], MinDate) AS date), 120),'[') + N' = SUM(CASE WHEN CAST([tiu].[UnitImportDate] AS date) = ' + QUOTENAME(CONVERT(char(10), CAST(DATEADD(DAY,[n], MinDate) AS date), 120),'''') + N' THEN 1 ELSE 0 END)'
                            FROM MinMaxDates CROSS JOIN [eTally]
                            ORDER BY [eTally].[n]
                            FOR XML PATH(''),TYPE).value('./text()[1]','nvarchar(max)'),1,2,'');

SELECT @SQLCmd = REPLACE(@SQLCmdTemplate,'~DateValues~',@DateValues);

EXEC sys.sp_executesql @SQLCmd;

drop table #temp_Imports
drop table #temp_ImportUnits

woody_rd Ten Centuries Points: 1012 More actions · Answer 6

Nice! Some of that is a bit over my head, but it does work! I'm going to have to really dig in to that solution to better understand it! Thanks, again!