Pivot Sql with no aggregate-CodePudding

I learned I can't pivot text without aggregation max() & min().

I am trying to figure out a workaround but the answers to similar questions are sailing over my head. would anyone have tips to workaround that?

data table:

pax	codex	mis
dog1	hair	10
dog1	face	10
dog1	eye	5
dog1	smell	7
dog1	yellow	7
dog1	green	8
dog1	blue	9
dog1	tan	10

desired output:

pax	10	10	5	7	7	8	9	10
dog1	hair	face	eye	smell	yellow	green	blue	tan

actual outcome:

pax	10	5	7	8	9
dog1	hair	eye	smell	green	blue

I used this code:

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols   QUOTENAME(mis)   ',' FROM (select distinct mis from #dd) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
   select   pax,codex,mis from #dd


) src
pivot 
(
    max(codex) for mis in ('   @cols   ')
) piv'

execute(@query)

CodePudding user response：

Try this

Select
  pax,
  concat(mis, '-', rn) as mis_new,
  codex
from (
    Select
      pax,
      mis,
      row_number() over (partition by pax, mis 
          order by mis ) rn 
    from table
) t
pivot (
  max(codex) for (
    mis_new in ('10-1','10-2', '5-1','7-1','7-2','8-1','9-1','10-3')
) pvt

CodePudding user response：

It looks like standard conditional aggregation over a row-number would serve better:

DECLARE @cols  AS NVARCHAR(MAX) =
(
  SELECT CONCAT(',MIN(CASE WHEN mis = ', QUOTENAME(mis, ''''), ' AND rn = ', rn, ' THEN codex END) ', QUOTENAME(mis))
  FROM (
      SELECT *,
        ROW_NUMBER() OVER (PARTITION BY pax ORDER BY mis) rn
      FROM dd
  ) as tmp
  GROUP BY mis, rn
  FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)');

DECLARE @query AS NVARCHAR(MAX) = '
SELECT
  pax'   @cols   '
FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY pax ORDER BY mis) rn
    FROM dd
) dd
GROUP BY pax;
';

PRINT @query; --for testing

EXEC sp_executesql
  @query;

db<>fiddle

Note the use of FOR XML to aggregate. Variable coalescing should not be used, due to unpredictability.