¿Cómo pivotar el número desconocido de columnas y no agregar en SQL Server?

Tengo una consulta que devuelve préstamos a los clientes con los nombres de las garantías asociadas como en el siguiente (1), pero quiero tener un solo número de préstamo distinto en una fila y los nombres de las garantías a un lado como en el otro ejemplo (2). He estado jugando pivotando pero no puedo entenderlo porque no tengo una columna agregada y no sé cuántos números de préstamo obtendré ni cuántas garantías puede tener cada préstamo. ¿¿¿Como hacer eso??? Posible en SQL Server 2012?

Gracias

(1)

loanid|name |Address | 1 |John |New York| 1 |Carl |New York| 1 |Henry |Boston | 2 |Robert|Chicago | 3 |Joanne|LA | 3 |Chris |LA | 

(2) Necesito algo como esto

 loanid|name |address |name |address |name|address| 1 |Jonh |New York |Carl |New York|Henry|Boston| 2 |Robert|Chicago | 3 |Joanne|LA |Chris|LA| 

Datos de prueba

 DECLARE @TABLE TABLE (loanid INT,name VARCHAR(20),[Address] VARCHAR(20)) INSERT INTO @TABLE VALUES (1,'John','New York'),(1,'Carl','New York'),(1,'Henry','Boston'), (2,'Robert','Chicago'),(3,'Joanne','LA'),(3,'Chris','LA') 

Consulta

 SELECT loanid ,ISNULL(name1, '') AS name1 ,ISNULL(Address1, '') AS Address1 ,ISNULL(name2, '') AS name2 ,ISNULL(Address2, '') AS Address2 ,ISNULL(name3, '') AS name3 ,ISNULL(Address3, '') AS Address3 FROM ( SELECT loanid ,'name' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) AS Cols , name AS Vals FROM @TABLE UNION ALL SELECT loanid ,'Address' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) , [Address] FROM @TABLE ) t PIVOT (MAX(Vals) FOR Cols IN (name1, Address1,name2,Address2,name3,Address3) )P 

Conjunto resultante

 ╔════════╦════════╦══════════╦═══════╦══════════╦═══════╦══════════╗ ║ loanid ║ name1 ║ Address1 ║ name2 ║ Address2 ║ name3 ║ Address3 ║ ╠════════╬════════╬══════════╬═══════╬══════════╬═══════╬══════════╣ ║ 1 ║ John ║ New York ║ Carl ║ New York ║ Henry ║ Boston ║ ║ 2 ║ Robert ║ Chicago ║ ║ ║ ║ ║ ║ 3 ║ Joanne ║ LA ║ Chris ║ LA ║ ║ ║ ╚════════╩════════╩══════════╩═══════╩══════════╩═══════╩══════════╝ 

Actualización para columnas dinámicas

 DECLARE @Cols NVARCHAR(MAX); SELECT @Cols = STUFF(( SELECT DISTINCT ', ' + QUOTENAME(Cols) FROM ( SELECT loanid ,'name' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) AS Cols , name AS Vals FROM @TABLE UNION ALL SELECT loanid ,'Address' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) , [Address] FROM @TABLE ) t GROUP BY QUOTENAME(Cols) FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') DECLARE @Sql NVARCHAR(MAX); SET @Sql = 'SELECT ' + @Cols + ' FROM ( SELECT loanid ,''name'' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) AS Cols , name AS Vals FROM @TABLE UNION ALL SELECT loanid ,''Address'' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) , [Address] FROM @TABLE ) t PIVOT (MAX(Vals) FOR Cols IN (' + @Cols + ') )P' EXECUTE sp_executesql @Sql 

Nota

Esto no funcionaría con los datos de muestra dados en mi respuesta, ya que usa una variable de tabla y no es visible para sql dynamic ya que tiene su propio scope. pero esta solución funcionará en una tabla de servidor sql normal.

Además, el orden en que se seleccionan las columnas será ligeramente diferente.

Si bien la respuesta de M.Ali le dará el resultado, ya que está usando SQL Server 2012, anularía las columnas de name y address ligeramente diferentes para obtener el resultado final.

Como está utilizando SQL Server 2012, puede usar CROSS APPLY Apply with VALUES para desvincular estas múltiples columnas en varias filas. Pero antes de hacer eso, usaría row_number() para obtener el número total de nuevas columnas que tendrá.

El código para “UNPIVOT” los datos usando CROSS APPLY se ve así:

 select d.loanid, col = c.col + cast(seq as varchar(10)), c.value from ( select loanid, name, address, row_number() over(partition by loanid order by loanid) seq from yourtable ) d cross apply ( values ('name', name), ('address', address) ) c(col, value); 

Ver SQL Fiddle con demostración . Esto va a tener sus datos en un formato similar a:

 | LOANID | COL | VALUE | |--------|----------|----------| | 1 | name1 | John | | 1 | address1 | New York | | 1 | name2 | Carl | | 1 | address2 | New York | | 1 | name3 | Henry | | 1 | address3 | Boston | 

Ahora tiene una sola columna COL con todos sus nuevos nombres de columna y los valores asociados también están en una sola columna. Los nuevos nombres de columna ahora tienen un número al final (1, 2, 3, etc.) basado en cuántas entradas totales tiene por loanid . Ahora puedes aplicar PIVOT:

 select loanid, name1, address1, name2, address2, name3, address3 from ( select d.loanid, col = c.col + cast(seq as varchar(10)), c.value from ( select loanid, name, address, row_number() over(partition by loanid order by loanid) seq from yourtable ) d cross apply ( values ('name', name), ('address', address) ) c(col, value) ) src pivot ( max(value) for col in (name1, address1, name2, address2, name3, address3) ) piv; 

Ver SQL Fiddle con demostración . Finalmente, si no sabe cuántos pares de Name y Address tendrá, entonces puede usar SQL dynamic:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) from ( select row_number() over(partition by loanid order by loanid) seq from yourtable ) d cross apply ( select 'Name', 1 union all select 'Address', 2 ) c (col, so) group by seq, col, so order by seq, so FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT loanid,' + @cols + ' from ( select d.loanid, col = c.col + cast(seq as varchar(10)), c.value from ( select loanid, name, address, row_number() over(partition by loanid order by loanid) seq from yourtable ) d cross apply ( values (''name'', name), (''address'', address) ) c(col, value) ) x pivot ( max(value) for col in (' + @cols + ') ) p ' exec sp_executesql @query; 

Ver SQL Fiddle con demostración . Ambas versiones dan un resultado:

 | LOANID | NAME1 | ADDRESS1 | NAME2 | ADDRESS2 | NAME3 | ADDRESS3 | |--------|--------|----------|--------|----------|--------|----------| | 1 | John | New York | Carl | New York | Henry | Boston | | 2 | Robert | Chicago | (null) | (null) | (null) | (null) | | 3 | Joanne | LA | Chris | LA | (null) | (null) | 
 SELECT DISTINCT loanid ,STUFF((SELECT DISTINCT ',' + name +' ('+address+')' FROM table a WHERE a.loanid = b.loanid FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') FROM table b 

Esto pondría

 loanid | name(address) 1 | name (address),name2 (address2),name3........ 2 | name (address),name2 (address2),name3........ 3 | name (address),name2 (address2),name3........