How do I show all combinations in one row?

Question

I tried to find a similar problem here but couldn't. This is a very simplified version of my problem. I have this table A

And I have another table:

I want to show results like this:

The number of columns here (in the last resultset) will differ according to how many combinations there are for each ID. Is this even possible to do? I have a limited amount of understanding with sql pivot,but not sure this is actually a pivot. Any ideas are welcome.

Answer 1

You are right by using Pivot u can transpose rows to columns.

CREATE TABLE #TableA
  (ID   INT,NAME VARCHAR(100))

INSERT INTO #TableA
VALUES     (1000,'Anna'),(2000,'Peter')

CREATE TABLE #TableB
  (ID    INT,City  VARCHAR(50),dates VARCHAR(50))

INSERT INTO #TableB
VALUES      (1000,'New york','2014-05-10' ),(1000,'Atlanta','2014-06-11'),
            (1000,'Los ang','2014-09-11'),(2000,'seattle','2014-02-10'),
            (2000,'miami','2014-03-11') 

SELECT id,
       Max([city1])  [City],
       Max([dates1]) [Dates],
       Max([city2])  [City],
       Max([dates2]) [Dates],
       Max([city3])  [City],
       Max([dates3]) [Dates]
FROM   (SELECT 'city'+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY B.id ORDER BY dates)) cityrn,
               'dates'+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY B.id ORDER BY dates)) datesrn,
               A.id,A.NAME,B.City,B.dates
        FROM   #TableB B
               JOIN #TableA a
                 ON A.id = B.ID) a 
               PIVOT (Max(City)
                    FOR cityrn IN([city1],[city2],[city3])) piv
               PIVOT (Max(dates)
                    FOR datesrn IN ([dates1],[dates2],[dates3])) piv1
        GROUP BY id

Dynamic Version :

DECLARE @citycols  VARCHAR(max)='',
        @datsecols VARCHAR(max)='',
        @aggcitycols  VARCHAR(max)='',
        @aggdatsecols VARCHAR(max)=''
DECLARE @sql          NVARCHAR(max)

SELECT @citycols += ',[' + crn + ']'
FROM  (SELECT DISTINCT 'city'+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY id ORDER BY dates)) crn
       FROM   #TableB) a

SELECT @datsecols += ',[' + drn + ']'
FROM   (SELECT DISTINCT 'dates'+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY id ORDER BY dates)) drn
        FROM   #TableB) a

SELECT @citycols = RIGHT(@citycols, Len(@citycols) - 1)
SELECT @datsecols = RIGHT(@datsecols, Len(@datsecols) - 1)

SELECT @aggcitycols += ',' + crn
FROM  (SELECT DISTINCT 'max(city'+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY id ORDER BY dates))+ ') City' crn
       FROM   #TableB) a

SELECT @aggdatsecols += ',' + drn
FROM   (SELECT DISTINCT 'max(dates'+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY id ORDER BY dates))+ ') [Date]' drn
        FROM   #TableB) a

SELECT @aggcitycols = RIGHT(@aggcitycols, Len(@aggcitycols) - 1)
SELECT @aggdatsecols = RIGHT(@aggdatsecols, Len(@aggdatsecols) - 1) 



SET @sql='  SELECT id,' + @aggcitycols + ',' + @aggdatsecols+ '
                 FROM   (SELECT ''city''+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY B.id ORDER BY dates)) cityrn,
                                ''dates''+ CONVERT(VARCHAR(50), Row_number() OVER(partition BY B.id ORDER BY dates)) datesrn
                                ,A.id,A.name,B.City,B.dates
                         FROM   #TableB b join #TableA a ON A.id = B.ID) a
                            PIVOT (Max(City)
                                 FOR cityrn IN(' + @citycols+ ')) piv
                            PIVOT (Max(dates)
                                 FOR datesrn IN (' + @datsecols+ ')) piv1
                         GROUP BY id
             '

EXEC Sp_executesql @sql 

OUTPUT

   +-----+--------+-----------+------------+---------+-----------+----------+-------------+
   | ID  |  NAME  | City      | Date       | City    |  Date     |  City    |   Date      |
   +-----+--------+-----------+------------+---------+-----------+----------+-------------+ 
   |1000 |  Anna  | New york  | 2014-05-10 | Atlanta | 2014-06-11|  Los ang |  2014-09-11 |
   +-----+--------+-----------+------------+---------+-----------+----------+-------------+ 
   |2000 |  Peter | seattle   | 2014-02-10 | miami   | 2014-03-11|  NULL    |   NULL      |
   +-----+--------+-----------+------------+---------+-----------+----------+-------------+