get the rows of a table in 1 row

Question

I have first table (the count of rows are varibale) and I want to create the second table. what is the efficient way? enter image description here

why would you want that ? or at least explain more please... — wam090, Feb 18 '15 at 08:32
You hade to describe the problem! How are we supposed to understand when you just say "I want it for a report"? I see no pattern in the wanted output... — jarlh, Feb 18 '15 at 08:42
The *efficient* way is to do this in your client instead of in SQL. — RBarryYoung, Feb 18 '15 at 12:10

score 1 · Accepted Answer · edited May 23 '17 at 12:12

First you have to bring your data to a more 'friendly' format:

;with 
data as
(
    -- replace this with your select
    select * from 
    (
        VALUES ('1', 'a', 'b'),
               ('2', 'c', 'd'),
               ('3', 'e', 'f')
    ) as data(aa,bb,cc)
    --------------------------------
),
dataAsXml as
(
    select CAST(STUFF((SELECT '<i>' + d.[aa] + '</i><i>' + d.[bb] + '</i><i>' + d.[cc] + '</i>' FROM data d FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,0,'') as XML) as data
),
dataAsList as
(
        select  x.i.value('for $i in . return count(../*[. << $i]) + 1', 'int') as 'Ord',
                x.i.value('.', 'NVARCHAR(100)') AS 'Value'
        from    dataAsXml
                CROSS APPLY [data].nodes('//i') x(i)
),
normalized AS
(
    select
        case (Ord - 1) % 3 + 1
            when 1 then 'aa'
            when 2 then 'bb'
            when 3 then 'cc'
        end  + cast((Ord - 1) / 3 + 1 as varchar(10)) as columnName, --fix here
        value
    from dataAsList
)
select * from normalized

In the query above you can plug in your data in data CTE to see the result. The output will have two columns one that stores your column names and one with values.

SQL Fiddle

From here you have to use a dynamic query where you pivot the obtained table for columnName in the list of all the column names. I won't describe this process because it has been done many times. Take a look at this answer:

Convert Rows to columns using 'Pivot' in SQL Server

Note: I didn't tested the performance of this method with large sets of data but from some points of view it's efficient.

score 0 · Answer 2 · answered Feb 18 '15 at 08:49

Try this one. I pivoted each of the columns then join them together in one row.

SELECT aa1,bb1,cc1,aa2,bb2,cc2,aa3,bb3,cc3 FROM
    (SELECT 1 id,[2]aa1,[3]aa2,[4]aa3 FROM(SELECT aa FROM tablea) AS A
    PIVOT(SUM(aa) FOR aa in([2],[3],[4])) AS pvt) A 
INNER JOIN
    (SELECT 1 id,[400]bb1,[200]bb2,[500]bb3 FROM(SELECT bb FROM tablea) AS A
    PIVOT(SUM(bb) FOR bb in([400],[200],[500])) AS pvt) B ON A.id=B.id
INNER JOIN
    (SELECT 1 id,[20]cc1,[25]cc2,[20]cc3 FROM(SELECT cc FROM tablea) AS A
    PIVOT(MIN(cc) FOR cc in([20],[25])) AS pvt) C ON B.id=C.id

get the rows of a table in 1 row

2 Answers2