How to pivot unknown number of columns & no aggregate in SQL Server?
I have query which returns clients loans with associated collateral names like below (1) but I want to have only one distinct loan number in a row and collateral names aside like on other example (2). Been playing with pivoting but cannot figure it out because I don't have aggregate column and I don't know how many loan numbers I will get neither how many collateral each loan may have. How to do that??? Possible in SQL Server 2012?
thanks
(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) I need something like this
loanid|name |address |name |address |name|address|
1 |Jonh |New York |Carl |New York|Henry|Boston|
2 |Robert|Chicago |
3 |Joanne|LA |Chris|LA|
Solution 1:
While M.Ali's answer will get you the result, since you are using SQL Server 2012 I would unpivot the name and address columns slightly different to get the final result.
Since you are using SQL Server 2012, you can use CROSS APPLY with VALUES to unpivot these multiple columns into multiple rows. But before you do that, I would use row_number() to get the total number of new columns you will have.
The code to "UNPIVOT" the data using CROSS APPLY looks like:
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);
See SQL Fiddle with Demo. This is going to get your data into a format similar to:
| LOANID | COL | VALUE |
|--------|----------|----------|
| 1 | name1 | John |
| 1 | address1 | New York |
| 1 | name2 | Carl |
| 1 | address2 | New York |
| 1 | name3 | Henry |
| 1 | address3 | Boston |
You now have a single column COL with all of your new column names and the values associated are also in a single column. The new column names now have a number at the end (1, 2, 3, etc) based on how many total entries you have per loanid. Now you can apply 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;
See SQL Fiddle with Demo. Finally if you don't know how many pairs of Name and Address you will have then you can use dynamic SQL:
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;
See SQL Fiddle with Demo. Both versions give a result:
| 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) |
Solution 2:
Test Data
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')
Query
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
Result Set
╔════════╦════════╦══════════╦═══════╦══════════╦═══════╦══════════╗
║ loanid ║ name1 ║ Address1 ║ name2 ║ Address2 ║ name3 ║ Address3 ║
╠════════╬════════╬══════════╬═══════╬══════════╬═══════╬══════════╣
║ 1 ║ John ║ New York ║ Carl ║ New York ║ Henry ║ Boston ║
║ 2 ║ Robert ║ Chicago ║ ║ ║ ║ ║
║ 3 ║ Joanne ║ LA ║ Chris ║ LA ║ ║ ║
╚════════╩════════╩══════════╩═══════╩══════════╩═══════╩══════════╝
Update for Dynamic Columns
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
Note
This wouldnt work with the given sample data in my answer, as it uses a table variable and it is not visible to dynamic sql since it has it own scope. but this solution will work on a normal sql server table.
Also the order in which columns are selected will be slightly different.