Join tables with a dynamic table and an unknown number of columns
-
I have a current schema that is fairly normal except one table can have multiple rows that need to pivot. I need to turn those rows into columns and am able to do that independent of the query with the following code that I found while searching for a solution.
So I want to include the address lines in the query. There can be from 1 to n address lines and it will always be unknown. I want this to be in a view if possible but if not something that can be used with tools like PowerBI.Here is the pivot code
DECLARE @sqlquery AS NVARCHAR(MAX);
DECLARE @PivotColumns AS NVARCHAR(MAX);--Get unique values of pivot column
SELECT @PivotColumns = COALESCE(@PivotColumns+',', '')+QUOTENAME(LineNumber)
FROM (SELECT DISTINCT LineNumber
FROM [dbo].[AddressLine]
) AS addrl;
--Create the dynamic query with all the values for
--pivot column at runtime
SET @sqlquery = N'SELECT AddressID, [GUID], '+@PivotColumns+' as Address_Line_'+SUBSTRING(@PivotColumns, 2, LEN(@PivotColumns)-2)+'
FROM [dbo].[AddressLine]
PIVOT( max ([Value])
FOR LineNumber IN ('+@PivotColumns+')) AS P';
EXEC sp_execute @sqlquery;Here is where I want the results.
SELECT
addr.[Name] AS Address_Name,
<< I want to insert the address lines here>>addr.City,
addr.Subdivision1Code,
addr.Subdivision2Code,
addr.Subdivision3Code,
addr.PostalCode,
addr.CountryCode
FROM dbo.[Address] AS addrt
-
Please post sample data and expected results. Follow the first link in my signature for details.
NOTE: Views cannot use dynamic SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thanks, I will work on that. the pivot query has a few problems that I just found out. Once I correct that I will repost.
-
/*
Ok, Here is the revision. I have a few things,
1) to pivot and join the results,
2) to rename columns in the pivot.
3) Make this callable so a product like PowerBI can use it.Problem, Address lines are an unknown quantity per address.
They can be 1 line, 3 lines, 21 lines or anything in between or more.
Any vehicle within SQL Server is available to me.TIA
*/IF OBJECT_ID('TempDB..#Address','U') IS NOT NULL
DROP TABLE #Address;CREATE TABLE #Address
([GUID] [NVARCHAR](250) NOT NULL,
[Name] [NVARCHAR](200) NOT NULL,
[City] [NVARCHAR](250) NOT NULL,
[Subdivision1Code] [NVARCHAR](250) NULL,
[Subdivision2Code] [NVARCHAR](250) NULL,
[PostalCode] [NVARCHAR](50) NULL,
[CountryCode] [NVARCHAR](3) NOT NULL
);INSERT INTO #Address
([GUID], [Name], [City], [Subdivision1Code], [Subdivision2Code], [PostalCode], [CountryCode])
VALUES
('TestName1', 'TestName1', 'Ontario', 'CA', 'San Bernardino', '91764', 'USA'),
('TestName2', 'TestName2', 'Upland', 'CA', 'San Bernardino', '91784', 'USA'),
('TestName3', 'TestName3', 'Anaheim', 'CA', 'Orange', '92801', 'USA');SELECT * FROM #address;
IF OBJECT_ID('TempDB..#AddressLine','U') IS NOT NULL
DROP TABLE #AddressLine;CREATE TABLE #AddressLine
([GUID] [NVARCHAR](250) NOT NULL,
[AddressID] [NVARCHAR](250) NOT NULL,
[LineNumber] [INT] NOT NULL,
[Value] [NVARCHAR](2000) NOT NULL
);
INSERT INTO #AddressLine
([GUID], [AddressID], [LineNumber], [Value])
VALUES
('T1Adr1', 'TestName1', 1, '1150 W 17th Street'),('T1Adr2', 'TestName1', 2, 'Suite 5'),
('T2Adr1', 'TestName2', 1, '1200 N Mountain Ave'),
('T3Adr1', 'TestName3', 1, '1126 N Hermosa Dr'),
('T3Adr2', 'TestName3', 2, 'Suite 16'),
('T3Adr3', 'TestName3', 3, 'Accounts Payable');SELECT *
FROM #addressline;-- Desired Result
/*
Name|AddressLine1|AddressLine2|AddressLine3|City|Subdivision1Code|Subdivision2Code|PostalCode|CountryCode
TestName1|1150 W 17th Street|Suite 5|NULL|Ontario|CA|San Bernardino|91764|USA
TestName2|1200 N Mountain Ave|NULL|NULL|Upland|CA|San Bernardino|91784|USA
TestName3|1126 N Hermosa Dr|Suite 16|Accounts Payable|Anaheim|CA|Orange|92801|USA
*/--Pivot query for address liness
-- I would like this to be part of the query to get the format I want.
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SELECT @cols = STUFF(
(
SELECT DISTINCT
','+QUOTENAME(LineNumber)
FROM #AddressLine FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @query = 'SELECT AddressID, '+@cols+' from
(
select AddressID, LineNumber, [Value]
from #AddressLine
) x
pivot
(
max([Value])
for LineNumber in ('+@cols+')
) p ';
EXECUTE (@query);--Query for address
SELECT [Name],
-- Insert address lines here as a single row from the pivot
[City],
[Subdivision2Code] AS [County],
[SubDivision1Code] AS [State],
[PostalCode],
[CountryCode]
FROM #Address; -
There's no need for a dynamic query here, since you are limited in the number of address lines you can have. I also replaced your PIVOT with a CROSSTAB, because it's more flexible. Specifically, PIVOT groups by all columns not specified in the pivot clause, and I did not want to include the GUID.
WITH AddressLines AS
(
SELECT
AddressID,
MAX( CASE WHEN LineNumber = 1 THEN [Value] END ) AS AddressLine1,
MAX( CASE WHEN LineNumber = 2 THEN [Value] END ) AS AddressLine2,
MAX( CASE WHEN LineNumber = 3 THEN [Value] END ) AS AddressLine3
FROM #addressline
GROUP BY AddressID
)
SELECT a.[Name], al.AddressLine1, al.AddressLine2, al.AddressLine3, City, Subdivision2Code AS County, Subdivision1Code AS [State], PostalCode, CountryCode
FROM #Address a
INNER JOIN AddressLines al
ON a.GUID = al.AddressID;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
The number of address lines is unknown for any customer. it is 1 to n and while typically is 4 or less can be more. I also wanted to extend this to other problems of the same nature. If there is no solution I will just add address lines to the main table.
- fparker 20089 - Thursday, August 2, 2018 1:09 PM
The number for each customer may be unknown, but the max according to your sample is three. The sample also shows that you are always including all three columns regardless of how many address lines a customer has. If that still isn't enough, search on this website for "Dynamic Crosstab"
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply