SQL split results into one row

Question

I can not get how to split SQL result onto one row. Lets say I have a query:

select Titles, Values from myTable

With restult:

|  TitlesHeader   |   ValuesHeader  |
|title1           |value1           |
|title2           |value2           |
|title3           |value3           |

And I need in one row

|Title1Header | Value1Header | Title2Header | Value2Header | Title3Header | Values3Header|
|title1       |value1        |title2        |value2        |tilte3        | value3       |

Let's assume that I know number of rows. But in general it is possible generate dynamically? Or for example at the moment I know that are 5 rows, which I need to split into one row, but I know that in future it will not grow more than 10, and fill which not exists with nulls or 0.

Thanks in advance!!!

what Database are you using? Pivot syntax varies alot (and you're the 4th pivot question of the day, searching stackoverflow will give you an answer) — Twelfth, Jul 15 '14 at 18:23
There a lot of examples to split result of one column into one row. Since I have results of 2 column. — user3322394, Jul 15 '14 at 18:31
NB: SQL Pivot is for where you have a defined range of outputs (e.g. months of the year). What you're trying to do seems to be have everything in one row when you don't know how many rows you're starting with. That can only be done by Dynamic SQL (I'll provide shortly); but is probably a bad idea... what are you hoping to achieve by doing this? — JohnLBevan, Jul 15 '14 at 18:37
Yes, dynamical SQL query will solve, but guess I don't need it, while I know that at the moment I have for example 5 rows. But for sure this will no grows more than 10. Even if it will grow, knowing how query works in can be easily modified. — user3322394, Jul 15 '14 at 18:44
So at the the end I have a final table with 10 Titles and Values structure, like: Title1,Value1,Title2,Value2... and so on. And I have query which I receive results in multiple rows. So what I need fill final table. Yes I know that it is denormalization, which bad at all, but this is need, for final application. — user3322394, Jul 15 '14 at 18:47

JohnLBevan · Answer 1 · 2014-07-15T19:19:56.693

Try this:

Simple Version

SQL Fiddle: http://sqlfiddle.com/#!6/67446/3

with cte (r, t, v) as (
  select row_number() over (order by [title], [value]) r
  , [title]
  , [value]
  from myTable
)
select x1.t title1header
, x1.v value1header
, x2.t title2header
, x2.v value2header
, x3.t title3header
, x3.v value3header
from cte x1
inner join cte x2 on x2.r = 2
inner join cte x3 on x3.r = 3
where x1.r = 1

Dynamic SQL Version

Automatically copes with changes to number of rows, but not nice. SQL Fiddle: http://sqlfiddle.com/#!6/2afca/11

declare @sql nvarchar(max)
, @i bigint = 1
select @sql = coalesce(@sql + ',','select ')
+ coalesce(quotename([title],''''),'null') 
+ ' Title' + cast(@i as nvarchar) + 'Header'
+ ','
+ coalesce(quotename([value],''''),'null')
+ ' Value' + cast(@i as nvarchar) + 'Header'
, @i = @i + 1
from myTable
order by [title]

exec (@sql)

Pivot Table Version

(really horrendous / not recommended)

SQL Fiddle: http://sqlfiddle.com/#!6/67446/1

select 
  max([t1]) title1header
, max([v1]) value1header
, max([t2]) title2header
, max([v2]) value2header
, max([t3]) title3header
, max([v3]) value3header
from (
  select 1 y
  , 't' + cast(row_number() over (order by [title], [value]) as nvarchar) tr
  , 'v' + cast(row_number() over (order by [title], [value]) as nvarchar) vr
  , [title]
  , [value]
  from myTable
) x
pivot
(
  max([title]) for tr in ([t1], [t2], [t3])
) p1
pivot
(
  max([value]) for vr in ([v1], [v2], [v3])
) p2
group by y

It worked, let me check if I can fit in my final query. Guess I can set into schema default values like zero. For example if my final table which I have to fill consists of more columns that query results, so others columns will automatically filled with default value. Thank you Very much! — user3322394, Jul 15 '14 at 18:57

SQL split results into one row

1 Answers1