I’m trying to join two tables
ColumnNames ID |Name |Type |Status | ----------------------------------------------------- 1 |Fullname |varchar |Active 2 |Email |varchar |Active 3 |Position |varchar |Active 4 |Category |varchar |Active
ColumnValues ID |ColumnNameID |value |Status | ----------------------------------------------------- 1 |1 |Linda |Active 2 |2 |linda@email.com |Active 3 |3 |Soft Eng. |Active 4 |4 |Cat 1 |Active 5 |1 |Remil |Active 6 |2 |Remil@email.com |Active 7 |3 |Senior Soft Eng. |Active 8 |4 |Cat 2 |Active 9 |1 |Ash |Active 10 |2 |ash@email.com |Active 11 |3 |Soft Eng. |Active 12 |4 |Cat 1 |Active
and then transposing rows of data (from Name on ColumnNames table) as column names.
Now, I was told to use Pivot. I’ve successfully used pivot for transposing rows from a single table, but I’m confused in how to transpose rows from joined tables.
Below is the code that I’ve tried, but it gives me an error saying columns doesn’t exist
@query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ColumnName) from tm.ColumnName FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ColumnNameID, ' + @cols + ' from ( select i.ColumnNameID, i.ColumnValue from tm.ColumnValue i inner join tm.ColumnName a on i.ColumnNameID = a.ColumnNameID ) x pivot ( max(ColumnNameID) for ColumnValue in (' + @cols + ') ) p ' execute(@query)
Expected Result Should be
Fullname |Email |Position |Category | -------------------------------------------------------------------- Linda |linda@email.com |Soft Eng. |Cat 1 Remil |Remil@email.com |Senior Soft Eng. |Cat 2 Ash |ash@email.com |Soft Eng. |Cat 1
Am I utilizing pivot correctly on this scenario?
Advertisement
Answer
Consider a ranking with ROW_NUMBER
across each column value type (fullname, email, position, category) to be included in INNER JOIN
then run through PIVOT
. No need of dynamic building of SQL:
SELECT [Fullname], [Email], [Position], [Category] FROM ( SELECT i.ColumnValue, a.[Name], ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn FROM ColumnValues i INNER JOIN ColumnNames a ON i.ColumnNameID = a.ColumnNameID ) tbl PIVOT (MAX(ColumnValue) FOR [Name] IN ([Fullname], [Email], [Position], [Category]) ) pvt ORDER BY rn
For a dynamic query, build a @cols
string to place in specific places of pivot query.
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Name]) FROM ColumnNames c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') SET @query = 'SELECT ' + @cols + ' FROM ( SELECT i.ColumnValue, a.[Name], ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn FROM ColumnValues i INNER JOIN ColumnNames a ON i.ColumnNameID = a.ColumnNameID ) tbl PIVOT (MAX(ColumnValue) FOR [Name] IN (' + @cols + ') ) pvt ORDER BY rn' EXECUTE(@query)