Динамический SQL для отмены сводки данных с помощью перекрестного применения с несколькими столбцами

Microsoft SQL Server Management Studio версии 18.8

У меня есть таблица, в которой будут разные столбцы и имена столбцов. Мне нужно развернуть данные, чтобы потом сохранить их в другой таблице. К сожалению, это процесс, поскольку исходная таблица извлекается из Google Sheet.

Я просмотрел здесь несколько статей и ответов, но не смог успешно воспроизвести ни одну из них. Мне нужно отменить поворот на основе элемента, метки времени и местоположения. Тогда Q1, Q2, Q3 и т. Д. Должны быть отключены. Ниже приведен пример таблицы и запроса, которые позволят получить желаемые результаты. Мы будем очень благодарны за любую помощь в получении этого в динамическом SQL для будущих добавлений / изменений столбцов. Я открыт для использования UNPIVOT или любой другой функции для получения желаемых результатов. Фактическим источником данных будет постоянная таблица, а не временная таблица.

Создать таблицу

DROP TABLE IF EXISTS #test
CREATE TABLE #test (Item VARCHAR(16), Timestamp DATETIME, Location VARCHAR(2), Q1 VARCHAR(3), Q2 VARCHAR(3), Q3 VARCHAR(3))
INSERT INTO #test VALUES('Stapler','2021-04-14 12:00:00.000', 'US','Yes','No','Yes'),
                        ('Paper','2021-04-10 16:00:00.000', 'CA','No','Yes','Yes'),
                        ('Pen','2021-04-06 15:00:00.000','MX','Yes','Yes','No')

Отмена сводки с помощью перекрестного применения

 SELECT A.Item,
           A.Timestamp,
           A.Location,
           B.*
      FROM #test AS A
    CROSS APPLY
    (
     VALUES ('Q1', A.Q1),
            ('Q2', A.Q2),
            ('Q3', A.Q3)
    ) B (Question,Answer)

sql sql-server unpivot cross-apply
person MonkeyMonkey    schedule 28.04.2021    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Вы можете использовать немного JSON для динамической отмены разворота данных. Если не 2016+ ... есть аналогичный XML-подход.

Пример

Select A.Item
      ,A.Timestamp
      ,A.Location
      ,B.*
 From  #test A
 Cross Apply (
                Select Question = [Key]
                      ,Answer   = [Value]
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                 Where [Key] not in ('Item','Timestamp','Location')
             ) B

Результаты

введите описание изображения здесь

ИЗМЕНИТЬ - Обновление для версии XML

Select A.Item
      ,A.Timestamp
      ,A.Location
      ,C.*
 From  #test A
 Cross Apply ( values ((Select A.* for XML RAW,Type)) )B(XMLData)
 Cross Apply (
                Select Question = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Answer   = xAttr.value('.','varchar(max)')
                 From XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)', 'varchar(100)')  not in ('Item','Timestamp','Location')
             ) C
person John Cappelletti    schedule 28.04.2021
comment
Это отлично! Метод JSON работал отлично. Мне нужно больше узнать о том, как работает JSON и функции, которые вы в нем использовали. Я пока ни в чем не видел этого метода. - person MonkeyMonkey; 28.04.2021
comment
@MonkeyMonkey Всегда рад помочь, погружение в пул JSON стоит вашего времени. - person John Cappelletti; 28.04.2021
comment
Довольно аккуратно. Добавьте ,TYPE в версию XML, если вы делаете (Select A.* for XML RAW, TYPE), вам не нужно конвертировать. Также text() быстрее, чем . - person Charlieface; 28.04.2021