sql query to show results vertically

Question

How to transform this:

ID   Name    Description
1    Test1a   TestDesc1a
1    Test1b   TestDesc1b
2    Test2a   TestDesc2a
2    Test2b   TestDesc2b

into this:

ID   Column          1            2
1    Name          test1a       test1b
1    Description   testDesc1a   testDesc1b

Why do you need to do this in SQL? How are you using this data once you get it out of the database? It might be easier to perform this transformation on the client side. — Tony, Apr 21 '12 at 20:19

score 0 · Answer 1 · answered Apr 21 '12 at 19:39

0

You ask for complex pivoting table. Read about this here: http://msdn.microsoft.com/en-us/library/ms177410.aspx

answered Apr 21 '12 at 19:39

mordka

392
3
11

score 0 · Answer 2 · answered Apr 21 '12 at 19:53

0

You need to use a PIVOT query.

A basic discussion of PIVOT queries can be found at [MSDN][1].

answered Apr 21 '12 at 19:53

David W

10,062
34
60

score 0 · Answer 3 · answered Apr 21 '12 at 21:09

This is a tricky question to solve, however the output specified is not proper/ conflicting. Below is similar solution which you can try

Sample Table creation:

CREATE TABLE [dbo].[TestTable](
[Id] [int] NULL,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL)

Inserting sample values:

INSERT INTO TestTable VALUES (1,'Test1a','TestDesc1a')
INSERT INTO TestTable VALUES (2,'Test1b','TestDesc1b')
INSERT INTO TestTable VALUES (3,'Test2a','TestDesc2a')
INSERT INTO TestTable VALUES (4,'Test2b','TestDesc2b')

Query to get the desired output using Pivot:

SELECT  'Name' AS [Column], [1], [2],[3],[4]
FROM
(SELECT Name, id from TestTable) AS ST
PIVOT
(Max(Name) FOR ID IN ([1], [2],[3],[4])) AS PT

UNION

SELECT  'Description' AS [Column], [1], [2],[3],[4]
FROM
(SELECT  id,[Description] from TestTable) AS ST
PIVOT
(Max([Description]) FOR ID IN ([1], [2],[3],[4])) AS PT
ORDER BY [Column] DESC

OutPut:

Column         1           2          3           4
Name           Test1a      Test1b     Test2a      Test2b
Description    TestDesc1a  TestDesc1b TestDesc2a  TestDesc2b

Hope this helps to solve your question.

score 0 · Answer 4 · answered May 02 '12 at 15:57

You can use a static PIVOT if you only are going to have a few columns but I am guessing that you will have more than 2 IDs so you will probably want to use a Dynamic PIVOT for this query. Using a dynamic pivot will allow you to have more than the two ids you provided, it will grab all of the Ids from the table and then PIVOT:

create table temp
(
    id int,
    name varchar(10),
    description varchar(20)
)

insert into temp values (1, 'Test1a', 'TestDesc1a')
insert into temp values (1, 'Test1b', 'TestDesc1b')
insert into temp values (2, 'Test2a', 'TestDesc2a')
insert into temp values (2, 'Test2b', 'TestDesc2b')


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.id) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ''Name'' as [Column], ' + @cols + ' from 
            (
                select id
                    , name
                from temp
           ) x
            pivot 
            (
                 max(name)
                for id in (' + @cols + ')
            ) p 
            UNION 
            SELECT ''Description'' as [Column], ' + @cols + ' from 
            (
                select id
                    , description
                from temp
           ) x
            pivot 
            (
                 max(description)
                for id in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

Results:

Column          1             2
Description     TestDesc1b    TestDesc2b
Name            Test1b        Test2b

sql query to show results vertically

4 Answers4