CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user123
user123

Reputation: 43

How to format select query output

I have table like this below, 

Type             Model       Year    
121232323        Test1       2000
121232323        Test2       2001

I want output like below, how to write query for that.?

121232323       Test1      Test2
                2000       2001

Upvotes: 0

Views: 94

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Another method other than PIVOT is to use conditional aggregation:

SELECT
    Type,
    Test1 = MAX(CASE WHEN Model = 'Test1' THEN [year] END),
    Test2 = MAX(CASE WHEN Model = 'Test2' THEN [year] END)
FROM tbl
GROUP BY Type

If you have unlimited number of Models you can do it using dynamic crosstab:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    Type' + CHAR(10) +
(SELECT DISTINCT
'   , MAX(CASE WHEN Model =''' + Model +  ''' THEN [year] END) AS ' + QUOTENAME(Model) + CHAR(10)
FROM tbl
FOR XML PATH('')
) +
'FROM tbl
GROUP BY Type;';

PRINT(@sql);
EXEC(@sql);

ONLINE DEMO

Upvotes: 2

sagi
sagi

Reputation: 40491

I don't know about Originalnr , but you can do this with conditional aggregation if you have a limited amount of tests :

SELECT t.Type,
       MAX(CASE WHEN t.Model = 'Test1' THEN t.Year END) as Test1,
       MAX(CASE WHEN t.Model = 'Test2' THEN t.Year END) as Test2
FROM YourTable t
GROUP BY t.Type

Upvotes: 2

mohan111
mohan111

Reputation: 8865

Using Simple Pivot we can achieve 

DECLARE @Table1 TABLE 
    ( Type  int,  Model  varchar(5),  Year  int)
;

INSERT INTO @Table1
    ( Type ,  Model ,  Year )
VALUES
    (121232323, 'Test1', 2000),
    (121232323, 'Test2', 2001)
;


select Type,[Test1],[Test2] from @Table1
PIVOT (MAX(YEAR) FOR MODEL IN ([Test1],[Test2]))PVT

Upvotes: 2

Related Questions