Запрос на вывод результатов в одну строку для всех комбинаций
Я хочу преобразовать вывод в одну строку из нескольких столбцов. Как мне обновить прикрепленный код.
Это мой текущий вывод:
OrigSamp DupSamp1
23407 23414
23420 23424
23420 23430
23420 23431
Это ожидаемый результат, который мне нужен, т.е. для каждого значения в первом столбце OrigSamp соответствующие значения в столбцах 2,3,4 должны быть в одной строке. Если нет значения, верните NULL.
OrigSamp DupSamp1 DupSamp2 DupSamp3
23407 23414 NULL NULL
23420 23424 23430 23431
Мой код ниже: любая помощь приветствуется.
declare @tbl table(OrigSamp int, DupSamp1 int, OrigRes varChar(10), DupRes1 varChar(10))
INSERT INTO @tbl
(OrigSamp,DupSamp1,OrigRes, DupRes1 )
SELECT
s.SAMPLE_NUMBER as [OrigSamp]
,s1.SAMPLE_NUMBER as [DupSamp1]
,r.FORMATTED_ENTRY as [OrigRes]
,r1.FORMATTED_ENTRY as [DupRes1]
FROM
SAMPLE s
INNER JOIN SAMPLE s1 on s.SAMPLE_NUMBER = s1.PARENT_SAMPLE
INNER JOIN RESULT r on s.SAMPLE_NUMBER = r.SAMPLE_NUMBER
INNER JOIN RESULT r1 on r1.SAMPLE_NUMBER = s1.SAMPLE_NUMBER
WHERE
s.LOCATION = 'TAURANGA'
and s.STATUS = 'C' and s1.STATUS = 'C'
and s1.SAMPLE_NAME = 'DUP'
and s.PARENT_SAMPLE = 0
and r.ANALYSIS in ('BC-B19_ISSUE_6-CENTR','BCPSD-ASTM_C136-CENTR')
and r1.ANALYSIS in ('BC-B19_ISSUE_6-CENTR','BCPSD-ASTM_C136-CENTR')
and r.NAME = 'Bitumen content'
and r1.NAME = 'Bitumen content'
declare @pivotcols varchar(200) = (STUFF((SELECT distinct ',' + QUOTENAME(c.rn)
FROM (select *
, 'DupSamp' + cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
@tbl) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,''))
declare @query varchar(max);
select *
into #temp1
from @tbl
set @query =
'select OrigSamp,' + @pivotcols + ' from
(select OrigSamp,DupSamp1 as [samp1],''DupSamp''
+ cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
#temp1
)t
pivot
(
max(samp1) for rn in (' + @pivotcols + ')
)p'
exec(@query)
drop table #temp1
Current output with the above code:
OrigSamp DupSamp1 DupSamp2 DupSamp3
23420 23424 23430 23431
required output
OrigSamp DupSamp1 DupSamp2 DupSamp3 OrigRes DupRes1 DupRes2 DupRes3
23420 23424 23430 23431 6.2 6.9 6.1 6.6
Я изменил ваш вопрос, чтобы изображения были видны без щелчка. Вы можете захотеть узнать, как это делается, но изображения часто бесполезны для нас.
ПОЧЕМУ один ряд? Какова цель поместить все в одну строку? например что произойдет, если текущий результат равен 20 строкам? или 200 строк? Также; если это для потребления через веб-страницу или отчет, то SQL может быть не лучшим местом для достижения этого стиля вывода. NB: если есть фиксированное количество столбцов, пожалуйста, также сообщите нам, что это за число.
@SQLKG - добавьте любой код на вопрос. Кроме того, вы получите больше ответов, если потратите время на публикацию полный, автономный пример. Это означает размещение структуры таблицы и нескольких образцов строк как текст, а не изображения. Существуют инструменты, упрощающие это, например dbfiddle.uk или sqlfiddle.com (у которых есть опция «Текст в DLL», позволяющая вставлять табличные данные и автоматически генерировать операторы CREATE TABLE и INSERT).
@Dale K, минимальный код ниже. ВЫБЕРИТЕ s.SAMPLE_NUMBER как [OrigSamp], s1.SAMPLE_NUMBER как [DupSamp1] FROM SAMPLE s INNER JOIN SAMPLE s1 на s.SAMPLE_NUMBER = s1.PARENT_SAMPLE, ГДЕ s.LOCATION = 'TAURANGA' и s.STATUS = 'C' и s1. Состояние = 'c' и s1.sample_name = 'dup' и s.carent_sample = 0 вывода origsampdupsamp1 23407 23420 23420 23424 23420 23420 23424 23420 23430 23420 2343 Ожидаемый Origsamp Dupsamp1 Dupsamp2 Dupsamp3 23407 23414 NULL NULL 23420 23424 23430 23431
@Dale Обновил мой исходный вопрос с минимальным кодом. Это помогает?
@Paul Maxwell, по одной строке для каждого значения в первом столбце. Первый столбец — родительская выборка. Остальные столбцы являются дочерними. Поэтому мне нужна одна строка для каждого родителя и их соответствующих детей. Родительский и дочерний элементы определяются полем SAMPLE.SAMPLE_NAME.
@Dale K Обновил формат. Это лучше?
Обновлен текущий набор данных. Спасибо за это, Дейл.
Известно ли максимальное количество столбцов «DupSamp»? Если да, то какое максимальное количество?
Если вы назначаете порядковые номера дубликатам под каждым родителем, используя что-то вроде Sequence = ROW_NUMBER() OVER(PARTITION BY s.SAMPLE_NUMBER ORDER BY s1.SAMPLE_NUMBER), вы можете затем GROUP BY s.SAMPLE_NUMBER и использовать условная агрегация для создания результата с выражениями вроде DupSamp1 = MAX(CASE WHEN Sequence = 1 THEN DupSamp END), DupSamp2 = MAX(CASE WHEN Sequence = 2 THEN DupSamp END), ...
@SOS Максимальное количество DupSamp, которое у меня будет, равно 5, то есть для каждого OrigSamp должны быть столбцы DupSamp1, DupSamp2, DupSamp3, DupSamp4, DupSamp5. Некоторые из них могут иметь нулевые значения
@ T N Я попробую и сообщу о результате.
Ответы 1
Вы можете получить желаемый результат, используя операцию динамического поворота.
Попробуйте приведенный ниже образец.
declare @tbl table(OrigSamp int, DupSamp1 int)
insert into @tbl
values(23407,23414)
,(23420,23424)
,(23420,23430)
,(23420,23431)
declare @pivotcols varchar(200) = (STUFF((SELECT distinct ',' + QUOTENAME(c.rn)
FROM (select *
, 'DupSamp' + cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
@tbl) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,''))
declare @query varchar(max);
select *
into #temp1
from @tbl
set @query =
'select OrigSamp,' + @pivotcols + ' from
(select OrigSamp,DupSamp1 as [samp1],''DupSamp''
+ cast(ROW_NUMBER()over(partition by OrigSamp order by OrigSamp) as varchar)rn
from
#temp1
)t
pivot
(
max(samp1) for rn in (' + @pivotcols + ')
)p'
exec(@query)
drop table #temp1
Я изменил код (в моем вопросе), как вы предложили. У меня есть еще один запрос: как мне обновить код, чтобы показать больше столбцов - пожалуйста, посмотрите мой код и вытекающий из него текущий и требуемый вывод в конце. Ценю твою помощь. ГХ
Запрос @SQLKG Coder является «динамическим», поэтому количество столбцов будет расширяться или сокращаться в соответствии с требованиями данных, и этот код предназначен для соответствия исходным демонстрационным данным и ожидаемому результату (что он и делает). см. dbfiddle.uk/…Вы переместили стойки ворот и добавлено OrigRes DupRes1 DupRes2 DupRes3 к ожидаемый результат, но у вас нет объяснения того, как и почему теперь существуют эти столбцы. Если вы хотите задать еще один вопрос, продолжайте, но на исходный вопрос уже дан ответ
@Paul В этом случае я попробую добавить дополнительные столбцы, используя код, предоставленный Coder1991. Как я могу подтвердить, что на мой первоначальный вопрос дан ответ. Я новичок в том, чтобы задавать вопросы в стеке.
Чтобы принять "Нажмите на галочку", подробнее см. помощь/принятие.
I tried PIVOT() but not too successful.Пожалуйста, покажите также ваш сводной запрос