CODEWITHSUNDEEP
sql-serversql-server-2012
Fonsi
Fonsi

Reputation: 59

Transpose in SQL Server 2012

I have the following:

Country | StateCity
--------+------------
USA     | MO
USA     | LA
USA     | OH
CANADA  | Ontario
CANADA  | Toronto

and am looking to transpose and unify the header with a result like

USA CANADA
MO  Ontario
LA  Toronto
OH

Upvotes: 1

Views: 52

Answers (4)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

But if you want DYNAMIC: Sql DEMO

First you need a temporal table to create a row_id

SELECT row_number() over (partition by [Country] order by [StateCity]) [rn], 
       [StateCity],
       [Country]
INTO temp
FROM State;

Then you can go the dynamic pivot route

Create the different columns

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

Create the pivot query: 

set @query = 'SELECT rn, ' + @cols + ' from 
            (
                select [rn]
                     , [StateCity]
                     , [Country]
                from temp
           ) x
            pivot 
            (
                 max(StateCity)
                for [Country] in (' + @cols + ')
            ) p ';

Execute it 

execute(@query);

OUTPUTs:

enter image description here

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

Assuming you don't want to go DYNAMIC

Declare @YourTable table (Country varchar(25),StateCity varchar(25))
Insert Into @YourTable values 
('USA','MO'),
('USA','LA'),
('USA','OH'),
('CANADA','Ontario'),
('CANADA','Toronto')

Select USA   =max(case when Country='USA'    then StateCity else '' end)
      ,CANADA=max(case when Country='CANADA' then StateCity else '' end)
 From ( Select *
              ,RowNr=Row_Number() over (Partition By Country Order By (Select NULL)) 
         From  @YourTable
       ) A
 Group By RowNr

Returns

USA CANADA
MO  Ontario
LA  Toronto
OH

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15175

This is more of a client side problem. The result you are looking for is not what is produced from a database query. The query below will get you closer and require less formating client side, however, without iterative processing you are not going to get a result set like that.

SELECT
    USA=CASE WHEN Country='USA' THEN StateCity ELSE NULL END,
    CANADA=CASE WHEN Country='CANADA' THEN StateCity ELSE NULL END
FROM
(
    SELECT DISTINCT Country,StateCity FROM Table WHERE Country IN('USA','CANADA')
)AS X

Upvotes: 0

Sean H
Sean H

Reputation: 267

For testing purposes:

CREATE TABLE dbo.Country (
    Country VARCHAR(10),
    StateCity VARCHAR(10)
);

INSERT INTO dbo.Country (Country, StateCity) VALUES ('USA', 'MO');
INSERT INTO dbo.Country (Country, StateCity) VALUES ('USA', 'LA');
INSERT INTO dbo.Country (Country, StateCity) VALUES ('USA', 'OH');
INSERT INTO dbo.Country (Country, StateCity) VALUES ('CANADA', 'Ontario');
INSERT INTO dbo.Country (Country, StateCity) VALUES ('CANADA', 'Toronto');

You can use one of the following solutions:

PIVOT

-- to follow

CASE (I am still working on merging the results to get rid of the NULLS)

SELECT 
    USA, 
    CANADA 
FROM 
(
    SELECT 
        CASE WHEN Country='USA' THEN StateCity END AS USA,
        CASE WHEN Country='CANADA' THEN StateCity END AS CANADA
    FROM 
        dbo.Country
) A
    GROUP BY 
        USA, 
        CANADA;

Upvotes: 0

Related Questions