I'm feeling horribly stupid. I can't for the life of me figure out how to transpose the simplest of all cases from a column to a row. All pivot/unpivot whatever examples I find shows complex cases with aggregation and what not, but how the heck does one just transform this:

into this:

It should be easy??

CodePudding user response：

Given a table like this:

create table so73413517 (
  value char(1)
)

insert so73413517(value) values('a'),('b') 
--,('c') -- etc.

If you will only ever want to return 1 row with 2 columns the max/min solution works best.

If you want 1 row but with more columns (but a fixed number) you can use row_number to give each row a unique, consecutive number, then you can do something like this

select max(case when r = 1 then value end) value1
, max(case when r = 2 then value end) value2
--, max(case when r = 3 then value end) value3 --etc.
from
(
    select row_number() over (order by value) r
    , value
    from so73413517
) x

Or

select [1], [2] 
--, [3] -- etc.
from
(
    select row_number() over (order by value) r
    , value
    from so73413517
) x
pivot
(
  max(value) for r in 
  (
    [1], [2]
    --,[3] --etc.
  )
) pvt

If you don't know how many rows are in the source/columns you want in your output, use dynamic SQL to create a column for each row (i.e. this uses the same logic as the pivot example above, but generates the query for the appropriate number of results:

declare @sql nvarchar(max) 

; with cte as (
    select count(1) rowNum from so73413517
    union all 
    select rowNum -1 from cte where rowNum  > 1
)
select @sql = coalesce(@sql   ',','')   quotename(rowNum) 
from cte 
order by rowNum

set @sql = 'select '   @sql   '
from
(
    select row_number() over (order by value) r
    , value
    from so73413517
) x
pivot
(
  max(value) for r in ('   @sql   ')
) pvt'

exec (@sql)

WARNING

That said; the above clearly isn't nice code... and normally that's a sign that something's wrong. If you're having to wrangle data in this way, look into whether you have the option to redesign your schema, or approach your underlying requirement with a different solution. If you're not sure how to do that feel free to ask on here.

i.e. This question feels like it may be an XY Problem.

Update

From the discussion in the comments, maybe ParseName would help.

declare @data nvarchar(128) = 'a,b,c'
 select parsename(tokenised, 3) value1
 , parsename(tokenised, 2) value2
 , parsename(tokenised, 1) value3
 from (select replace(@data, ',','.')) x(tokenised)

Note: ParseName is used to split apart instance.catalog.schema.table formatted names, so can only work with up to 4 x 128 char strings separated by dots.

More info on that trick and other options for your particular use case here: https://stackoverflow.com/a/70862904/361842

CodePudding user response：

with cte(val)as
(
  select 'a' union all
  select 'b'
)
select
  min(val) col1,max(val)col2
   from cte

Just for your example