Как скопировать неизвестное количество столбцов и нет агрегата в SQL Server?
У меня есть запрос, который возвращает кредиты клиентам с соответствующими именами обеспечения, как показано ниже (1)
но я хочу иметь только один отдельный номер кредита в строке и имена залога, как в другом примере (2). Играя с поворотным, но не могу понять, потому что у меня нет столбца агрегата, и я не знаю, сколько номеров займов я получу ни сколько залога, которое может иметь каждый кредит. Как это сделать??? Возможно в SQL Server 2012?
спасибо
(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) Мне нужно что-то вроде этого
loanid|name |address |name |address |name|address|
1 |Jonh |New York |Carl |New York|Henry|Boston|
2 |Robert|Chicago |
3 |Joanne|LA |Chris|LA|
Ответы
Ответ 1
Данные тестирования
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
Набор результатов
╔════════╦════════╦══════════╦═══════╦══════════╦═══════╦══════════╗
║ loanid ║ name1 ║ Address1 ║ name2 ║ Address2 ║ name3 ║ Address3 ║
╠════════╬════════╬══════════╬═══════╬══════════╬═══════╬══════════╣
║ 1 ║ John ║ New York ║ Carl ║ New York ║ Henry ║ Boston ║
║ 2 ║ Robert ║ Chicago ║ ║ ║ ║ ║
║ 3 ║ Joanne ║ LA ║ Chris ║ LA ║ ║ ║
╚════════╩════════╩══════════╩═══════╩══════════╩═══════╩══════════╝
Обновление для динамических столбцов
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
Примечание
Это не будет работать с данными примера в моем ответе, поскольку он использует переменную таблицы и не отображается в динамическом sql, так как имеет собственную область. но это решение будет работать на обычной таблице серверов sql.
Также порядок, в котором выбраны столбцы, будет немного отличаться.
Ответ 2
В то время как ответ M.Ali даст вам результат, поскольку вы используете SQL Server 2012, я бы отклонил столбцы name и address, немного отличающиеся от получите окончательный результат.
Поскольку вы используете SQL Server 2012, вы можете использовать CROSS APPLY с VALUES, чтобы развернуть эти несколько столбцов на несколько строк. Но прежде чем вы это сделаете, я бы использовал row_number(), чтобы получить общее количество новых столбцов.
Код для "UNPIVOT" данных с использованием CROSS APPLY выглядит так:
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);
Смотрите SQL Fiddle with Demo. Это позволит получить ваши данные в формате, подобном:
| LOANID | COL | VALUE |
|--------|----------|----------|
| 1 | name1 | John |
| 1 | address1 | New York |
| 1 | name2 | Carl |
| 1 | address2 | New York |
| 1 | name3 | Henry |
| 1 | address3 | Boston |
Теперь у вас есть один столбец COL со всеми вашими новыми именами столбцов, а связанные значения также находятся в одном столбце. Новые имена столбцов теперь имеют число в конце (1, 2, 3 и т.д.) В зависимости от того, сколько всего записей у вас есть за loanid. Теперь вы можете применить 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;
См. SQL Fiddle with Demo. Наконец, если вы не знаете, сколько пар name и address у вас будет, то вы можете использовать динамический 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;
См. SQL Fiddle with Demo. Обе версии дают результат:
| 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) |
Ответ 3
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
Это поставит
loanid | name(address)
1 | name (address),name2 (address2),name3........
2 | name (address),name2 (address2),name3........
3 | name (address),name2 (address2),name3........
