April 24, 2016 at 3:24 pm
I'm a little familiar with pivoting functions in SQL, but I'm not having any luck trying to solve this one.
I have a table with a license number and license expiration date keyed to a registrationID. I am trying to write a query that will display the rows in columns, but I don't want the values to become the column names.
RegID License# LicenseExpDate
1 ABC123 12/31/16
2 DEF456 10/20/16
2 BFG999 11/11/16
I'd like to output it like
RegID License1 LicenseExp1 License2 LicenseExp2 License3 LicenseExp3
1 ABC123 12/31/16 NULL NULL NULL NULL
2 DEF456 10/20/16 BFG999 11/11/16 NULL NULL
I am pretty sure it's going to be dynamic SQL, but I am not sure how to even name the columns correctly.
I have spots for 3 licenses so I want to display all three if they have them, and nulls if they don't have any, and any combo in between.
I'm trying this, but I know this is incorrect because the column headings don't exist as values in the db table:
SELECT 'RegID' AS RegistrationID,
License1, LicenseExpiration1, License2, LicenseExpireation2, License3, LicenseExpireation3
FROM
(SELECT RegID, LicenseNumber, LicenseExpiration
FROM LicenseNumbers) AS SourceTable
PIVOT
(
MIN(LicenseNumber)
FOR LicenseNumber IN (License1, License2, License3)
) AS PivotTable;
April 24, 2016 at 3:49 pm
I have a blog post detailing how to do this very thing: Smart Pivot Tricks: Multiple Pivots, Aliases, Dynamic Pivots
You need to build the column names dynamically, pivot twice, and then group by. It's simpler than it sounds.
Declare @Licenses Table (RegID int, License# varchar(6), LicenseExpDate Date)
Insert Into @Licenses
Values (1, 'ABC123', '12/31/16'),
(2, 'DEF456', '10/20/16'),
(2, 'BFG999', '11/11/16'),
(3, 'BFG998', '11/10/16'),
(3, 'BFG997', '11/09/16'),
(3, 'BFG996', '11/08/16');
With LicensesRows
As (Select RowID = ROW_NUMBER() Over (Partition By RegID Order By RegID),
RegID,
License#,
LicenseExpDate
From @Licenses)
Select RegID,
License1 = Min(License1),
LicenseExp1 = Min(LicenseExp1),
License2 = Min(License2),
LicenseExp2 = Min(LicenseExp2),
License3 = Min(License3),
LicenseExp3 = Min(LicenseExp3)
From (Select RegID,
'License' + Cast(RowID As varchar(10)) As LicenseNum,
'LicenseExp' + Cast(RowID As varchar(10)) As LicenseExpNum,
License#,
LicenseExpDate
From LicensesRows) As P
Pivot (Min(License#)
For LicenseNum In ([License1], [License2], [License3])
) As Pvt1
Pivot (Min(LicenseExpDate)
For LicenseExpNum In ([LicenseExp1], [LicenseExp2], [LicenseExp3])
) As Pvt2
Group By RegID;
April 24, 2016 at 6:56 pm
No need to use Pivot at all. Here's Robert's sample data in a temp table (using a temp table so I can show you how to index correctly for my solution).
IF OBJECT_ID('tempdb..#Licenses') IS NOT NULL DROP TABLE #Licenses;
CREATE TABLE #Licenses
(
RegID int NOT NULL,
License# varchar(6) NOT NULL,
LicenseExpDate Date NOT NULL
);
Insert Into #Licenses
Values (1, 'ABC123', '12/31/16'),
(2, 'DEF456', '10/20/16'),
(2, 'BFG999', '11/11/16'),
(3, 'BFG998', '11/10/16'),
(3, 'BFG997', '11/09/16'),
(3, 'BFG996', '11/08/16');
All you have to do is this:
WITH R AS
(
SELECT *, rnk = ROW_NUMBER() OVER (PARTITION BY RegID ORDER BY LicenseExpDate)
FROM #Licenses
)
SELECT
RegID,
MAX(CASE rnk WHEN 1 THEN [License#] END) AS License1,
MAX(CASE rnk WHEN 1 THEN LicenseExpDate END) AS LicenseExp1,
MAX(CASE rnk WHEN 2 THEN [License#] END) AS License2,
MAX(CASE rnk WHEN 2 THEN LicenseExpDate END) AS LicenseExp2,
MAX(CASE rnk WHEN 3 THEN [License#] END) AS License3,
MAX(CASE rnk WHEN 3 THEN LicenseExpDate END) AS LicenseExp3
FROM R
GROUP BY RegID;
You can learn more about this technique here: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
For optimal performance you would want this index in place:
CREATE UNIQUE NONCLUSTERED INDEX uq_L ON #licenses (RegID, LicenseExpDate) INCLUDE ([License#]);
-- Itzik Ben-Gan 2001
April 25, 2016 at 12:41 am
Here is a dynamic version of Alan's answer:
CREATE TABLE Licenses(
RegID INT,
License# VARCHAR(6),
LicenseExpDate DATE
);
INSERT INTO Licenses VALUES
(1, 'ABC123', '20161231'),
(2, 'DEF456', '20161020'),
(2, 'BFG999', '20161111'),
(3, 'BFG998', '20161110'),
(3, 'BFG997', '20161109'),
(3, 'BFG996', '20161108');
SELECT *,
rn = ROW_NUMBER() OVER(PARTITION BY regID ORDER BY LicenseExpDate)
INTO #ttLicenses
FROM Licenses;
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql =
'SELECT
RegID' + CHAR(10);
SELECT @sql = @sql +
' , MAX(CASE WHEN rn = ' + CONVERT(VARCHAR(10), rn) + ' THEN [License#] END) AS '
+ QUOTENAME('License' + CONVERT(VARCHAR(10), rn)) + CHAR(10) +
' , MAX(CASE WHEN rn = ' + CONVERT(VARCHAR(10), rn) + ' THEN LicenseExpDate END) AS '
+ QUOTENAME('LicenseExpDate' + CONVERT(VARCHAR(10), rn)) + CHAR(10)
FROM (
SELECT DISTINCT rn FROM #ttLicenses
) t
ORDER BY rn;
SELECT @sql = @sql +
'FROM #ttLicenses
GROUP BY RegID;'
;
PRINT (@sql);
EXEC sp_executesql @sql;
DROP TABLE Licenses;
DROP TABLE #ttLicenses;
April 26, 2016 at 1:41 pm
Awesome, thanks y'all! I'll check it all out. I did get it to work by putting the code into a view and joining to that, but I always want to learn new ways of seeing the same issue:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply