SQL QUERY Pivot and join

Question

I Have a Parent table Called data as

ID     Lat     Lon

and have a photo table as

ID    id_from_Data_table    photoPath

photo table has multiple records for each record in data able.

I would like to have a result set like this:

lat   lon    photopath1   photopath2   photopath3 ......

a photo path column for each record in photo table.

This is a very common question and there are plenty of answers demonstrating how to do the pivot. Do the join first, then the pivot. — jpw, Apr 13 '15 at 12:04
You can find a lot of similar questions by searching with "dynamic pivot". I would anyhow still recommend to do this outside the database, since it's really not the correct place of formatting the data like this — James Z, Apr 13 '15 at 12:04
i would appreciate if you draft a sample, I spent a lot of time but it looks like i am lacking some concept on this. — johnny, Apr 13 '15 at 12:08

score 1 · Accepted Answer · answered Apr 13 '15 at 12:30

Using dynamic SQL the query could look something like this:

DECLARE @cols  AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(rn)
                    FROM (
                    SELECT d.*, p.photoPath, rn = CONCAT('Photo Path ',ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY p.id)) 
                    FROM data d 
                    JOIN photo p ON d.ID = p.id_from_Data_table
                ) yourtable
                    GROUP BY rn
                    ORDER BY rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = N'
    SELECT lat, lon, ' + @cols + N' 
    FROM 
    (
       SELECT 
          d.*, 
          p.photoPath, 
          rn = CONCAT(''Photo Path '', ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY p.id)) 
       FROM data d 
       JOIN photo p ON d.ID = p.id_from_Data_table
    ) x
    PIVOT 
    (
       MAX(photoPath) FOR rn IN (' + @cols + N')
    ) p;'

EXEC sp_executesql @query;

Sample SQL Fiddle

SQL QUERY Pivot and join

1 Answers1