Click here to Skip to main content
14,209,828 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,
i need to do a dynamic pivot on column 1 distinct values in field_name change to columns in target and field_value values in column 2 become values in NEW target columns

i have my_source_table
row_id | foreign_key | Field_Name | field_value
1 | 1 | plant | flower
2 | 1 | clothing | pants
3 | 1 | country | usa
4 | 2 | plant | tree
5 | 2 | continent | europe
6 | 2 | country | france
7 | 3 | plant | flower
8 | 3 | country | brazil
9 | 3 | vehicle | bike
10 | 3 | season | summer
11 | 1 | season | spring


NEEDED:
foreign_key | plant | clothing| country |continent| vehicle | season
1 | flower| pants | usa | null | null | spring
2 | tree | null | france| europe| null | null
3 | flower| null | brazil| null | bike | summer

What I have tried:

i have tried a number of pivots but 
(1), i do not know how what to do about the aggregate function as i have nothing to aggregate and
(2), i do not know how to determine the column headers dynamically, based on distinct values in the my_source_table.field_name column   


SELECT [foreign_key], [plant], [clothing], [country], [continent], [vehicle], [season]
FROM 
(SELECT *
FROM my_source_table) a
PIVOT
(
Sum(count(field_value)
FOR vehicle IN
( ([plant],[clothing],[country],[continent],[vehicle],[season] )
) AS pvt
ORDER BY foreign_key
Posted
Updated 12-Jun-19 4:12am
Comments
CHill60 12-Jun-19 8:47am
   
I usually use MAX() when there is nothing to aggregate per se.
Also - I have an example of Dynamic SQL on my article Processing Loops in SQL Server[^]
In the meantime I'll try to construct you a specific answer

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I'll deal with your question in sections .. .firstly
Quote:
(1), i do not know how what to do about the aggregate function as i have nothing to aggregate
As I said in my comment, if there is nothing to aggregate then I usually use MAX() (or MIN()). This has the net effect of just returning the value you want. Here is an example:
SELECT [foreign_key], [plant], [clothing], [country], [continent], [vehicle], [season]
FROM 
(
	SELECT row_id, foreign_key, Field_Name, field_value
	FROM my_source_table
) a
PIVOT
(
	MAX(field_value)
		FOR Field_Name IN ([plant],[clothing],[country],[continent],[vehicle],[season] )
) AS pvt
ORDER BY foreign_key
Note in your attempt you had used FOR vehicle IN .... The column [vehicle] doesn't actually exist until after the PIVOT has been actioned, so you can't refer to it within the body of the PIVOT. Nor do you want to, you are converting the [Field_Name] values to columns so that's what needs to go in that clause.

Unfortunately, the query as it stands produces these unhelpful results...
foreign_key	plant	clothing	country	continent	vehicle	season
1	flower	NULL	NULL	NULL	NULL	NULL
1	NULL	pants	NULL	NULL	NULL	NULL
1	NULL	NULL	usa	NULL	NULL	NULL
1	NULL	NULL	NULL	NULL	NULL	spring
2	tree	NULL	NULL	NULL	NULL	NULL
2	NULL	NULL	NULL	europe	NULL	NULL
2	NULL	NULL	france	NULL	NULL	NULL
3	flower	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	brazil	NULL	NULL	NULL
3	NULL	NULL	NULL	NULL	bike	NULL
3	NULL	NULL	NULL	NULL	NULL	summer
We need to aggregate them in someway … e.g.
SELECT [foreign_key], MAX([plant]) AS [plant], MAX([clothing]) as [clothing], MAX([country]) as [country], MAX([continent]) as [continent], MAX([vehicle]) as [vehicle], MAX([season]) as [season]
FROM 
(
	SELECT row_id, foreign_key, Field_Name, field_value
	FROM my_source_table
) a
PIVOT
(
	MAX(field_value)
		FOR Field_Name IN ([plant],[clothing],[country],[continent],[vehicle],[season] )
) AS pvt
GROUP BY foreign_key
ORDER BY foreign_key
Note that I'm doing the aggregate on the dataset produced by the PIVOT, "as well as" the aggregate in the pivot. This produces your expected results
foreign_key	plant	clothing	country	continent	vehicle	season
1	flower	pants	usa	NULL	NULL	spring
2	tree	NULL	france	europe	NULL	NULL
3	flower	NULL	brazil	NULL	bike	summer

Quote:
(2), i do not know how to determine the column headers dynamically, based on distinct values in the my_source_table.field_name column
To do this you need to generate some Dynamic SQL. Let's look at the first bit that could change depending on the data...First let's add some extra data
insert into my_source_table (foreign_key, Field_Name, field_value) values (1, 'sense', 'smell')
So we know we're going to have to "construct" something that adds that new value to the bit of the SQL that currently says
SELECT [foreign_key], [plant], [clothing], [country], [continent], [vehicle], [season]
There are a few ways of constructing that list - because I want to add some square brackets this is probably the easiest
DECLARE @liststr NVARCHAR(MAX) = null
;WITH fieldValues AS
(
	SELECT DISTINCT Field_Name FROM my_source_table
)
SELECT @liststr = COALESCE(@liststr +'],[','') + Field_Name 
FROM fieldValues
SELECT '[' + @liststr + ']'
This will produce the results
[clothing],[continent],[country],[plant],[season],[sense],[vehicle]
Note that I can't control the order that these are returned in unless I ORDER BY the query. That ORDER BY has to be outside the CTE … ie. I can't have
SELECT DISTINCT Field_Name FROM my_source_table ORDER BY Field_Name
I have to use
FROM fieldValues  ORDER BY Field_Name
Also note that the variable starts as null and I have to add the missing bracket on at the end. Give it a try the other way to see why.

Now let's address the other bit that needs to change now we've added new data - the original query said
SELECT [foreign_key], MAX([plant]) AS [plant], MAX([clothing]) as [clothing], MAX([country]) as [country], MAX([continent]) as [continent], MAX([vehicle]) as [vehicle], MAX([season]) as [season]
This is a bit more fiddly as I need to refer to the field name twice and include the MAX function. Have a good look at the difference in the way that I do this. Also note that I want to ignore the first two characters when I use the results
DECLARE @liststr2 NVARCHAR(MAX) = null
;WITH MAXfieldValues AS
(
	SELECT DISTINCT Field_Name, Field_Name AS FN FROM my_source_table
)
SELECT @liststr2 = COALESCE(@liststr2,'')  +'],MAX([' + Field_Name + ']) AS [' + Field_Name
FROM MAXfieldValues ORDER BY Field_Name
SELECT SUBSTRING(@liststr2,3, LEN(@liststr2)) + ']'
This produces the results
MAX([clothing]) AS [clothing],MAX([continent]) AS [continent],MAX([country]) AS [country],MAX([plant]) AS [plant],MAX([season]) AS [season],MAX([sense]) AS [sense],MAX([vehicle]) AS [vehicle]
We already know what the rest of the query needs to look like so lets stitch it all together and run it
DECLARE @sql NVARCHAR(MAX) = N'SELECT [foreign_key], ' + SUBSTRING(@liststr2,3, LEN(@liststr2)) + ']' +
' FROM 
(
	SELECT row_id, foreign_key, Field_Name, field_value
	FROM my_source_table
) a
PIVOT
(
	MAX(field_value)
		FOR Field_Name IN ([' + @liststr + '] )) AS pvt
GROUP BY foreign_key
ORDER BY foreign_key'

Exec sp_executesql @sql
And the results...
FK	clothing	continent	country	plant	season	sense    vehicle
1	pants	NULL	usa	flower	spring	smell	NULL
2	NULL	europe	france	tree	NULL	NULL	NULL
3	NULL	NULL	brazil	flower	summer	NULL	bike
I really encourage you to work through the various sections to make sure you fully understand what is going on. There are links to documentation and other examples in the article link in my comment
   
Comments
Maciej Los 3 days ago
   
Great job!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190617.3 | Last Updated 12 Jun 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100