Динамические столбцы для таблицы
У меня есть таблица продаж:
SalesID. Date. Geography
1. 2020-01. Italy
1. 2020-01. France
1. 2020-01. UK
2. 2020-02. Italy
2. 2002-02. Canada
3. 2002-08. France
Я хотел бы получить такой результат:
SalesID. Date. Geography1 Geography2. Geography3
1. 2020-01. Italy. France. Uk
2. 2020-02. Italy. Canada. Null
3. 2020-08. France
Я знаю, как сделать поворот столбца, но количество географии не ограничено для идентификатора продаж. Как это сделать?
Не могли бы вы назвать максимальное количество регионов для продажи? В противном случае вам понадобится динамический SQL, что значительно усложнит задачу.
Для географии ограничений нет. Это точно моя проблема.
Таблицы и результаты запросов подобны типам в строго типизированных языках. Они не могут варьироваться. Если вам нужно разное количество столбцов, вам придется создать динамический SQL-запрос. Который не будет использоваться другими запросами, что поднимает вопрос, зачем вам это? Если вы хотите повернуть таблицу для целей отчетности, на самом деле это проще сделать на клиенте.
В SQL Server 2017 и более поздних версиях вы можете использовать STRING_AGG для объединения нескольких строк в одно значение. Можно написать select SalesID, Date, STRING_AGG('. ',Geography) as Geographies from Sales GROUP BY SalesID, Date. Это может помочь или нет. Зависит от того, какой инструмент отчетности у вас есть. Однако большинство из них могут отображать данные по столбцам или сводные результаты.
Я бы предложил поместить столбцы geography в одну строку или столбец JSON, а не в какое-то неопределенное количество столбцов.
Ответы 2
CREATE TABLE MyTable( SalesID INT, [Date] varchar(20), Geography VARCHAR(50) ); INSERT INTO MyTable(SalesID,[Date],Geography)VALUES (1,'2020-01','Italy'),(1,'2020-01','France'), (1,'2020-01','UK'),(2,'2020-02','Italy'),(2,'2020-02','Canada'), (3,'2020-08','France'); DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(a.Geography) FROM MyTable a FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT date, ' + @cols + ' from ( select SalesID ,Date , Geography from MyTable ) x pivot ( max(Geography) for Geography in (' + @cols + ') ) p ' execute(@query)
date | Canada | France | Italy | UK :------ | :----- | :----- | :---- | :--- 2020-01 | null | France | Italy | UK 2020-02 | Canada | null | Italy | null 2020-08 | null | France | null | null
db<>рабочий пример здесь
Есть ли способ переименовать столбцы как География 1, География 2,.., География N?
;WITH L AS(
SELECT SalesID, Date, Geography
from
(
SELECT 1 as SalesID, '2020-01' as Date, 'Italy' as Geography
UNION ALL
SELECT 1, '2020-01', 'France'
UNION ALL
SELECT 1, '2020-01', 'UK'
UNION ALL
SELECT 2, '2020-02', 'Italy'
UNION ALL
SELECT 2, '2020-02', 'Canada'
UNION ALL
SELECT 3, '2002-08', 'France'
)S
),
L1 AS(
SELECT SalesID, Date, Geography
from
(
SELECT 1 as SalesID, '2020-01' as Date, 'Italy' as Geography
UNION ALL
SELECT 1, '2020-01', 'France'
UNION ALL
SELECT 1, '2020-01', 'UK'
UNION ALL
SELECT 2, '2020-02', 'Italy'
UNION ALL
SELECT 2, '2020-02', 'Canada'
UNION ALL
SELECT 3, '2002-08', 'France'
)S
)
SELECT
L.SalesID,L.Date,
STUFF((SELECT '; ' + L1.Geography
FROM L1
WHERE L1.SalesID = L.SalesID
AND L1.Date = L.Date
FOR XML PATH('')), 1, 1, '') [Geography]
FROM L
GROUP BY L.SalesID, L.Date
ORDER BY 1
Вот вывод SQL-запроса в виде снимка экрана:
Опубликованное вами решение не соответствует желаемому результату OP.
Вам нужно будет использовать Dynamic Pivot с
ROW_NUMBER.