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);
' , 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;
'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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy