results to columns instead of rows

Question

I have query returns rows and I'd like to display in columns.

Select city from USA

city

Richmond

LA

Washington

Denver

 to city Richmond LA Washington Denver

Thanks

Answer 1

;with cte as (select city, 'City' + cast(row_number() over (order by ID) as varchar(10)) as CityRec from CitiesInfo)

select * from cte PIVOT (max(city) for CityRec IN ([City1],[City2],[City3],[City4])) pvt

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks for the fast reply.  By the way, what is ;with cte as()?

Answer 3

Hi !

CTE stands for Common Table Expression.

You can see here for more details.

Note that the statements just before the WITH keyword must ends with a ';'. That's why a ';' is often placed just before this keyword in that context.

JN.

---

Jean-Nicolas BERGER
http://blog.sqlserver.fr

Answer 4

;with cte is a Common Table Expression. I give you three good links to get up to speed with them:

CTE: Coolest T-SQL Enhancement

CTE and hierarchical queries

and also relatively recent blog by Denis Gobo

SQL Advent 2011 Day 5: Common Table Expressions

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Thanks.  By the way, I used 4 city names as example but if I have 40 cities, do I need to put in all cities ([City1],[City2]--[City40]) ?

Answer 6

In this case we're talking about dynamic SQL which is slightly more complex.

declare @SQL nvarchar(max), @Cols nvarchar(max)

select @Cols = stuff((select ', ' + quotename(CityRn) from (select 'City' + 
cast(row_number() over (order by ID) as varchar(10)) as CityRn

from Cities) X order by CityRn for XML PATH('')),1,2,'')

set @SQL = 'select * from (select City, ''City'' + 
cast(row_number() over (order by ID) as varchar(10)) as CityRn
FROM Cities) src

PIVOT (max(City) FOR CityRn IN (' + @Cols + ')) pvt'

print @SQL -- test

execute (@SQL)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Naomi,

Thanks for the reply.  By the way, that is CityRn that you used

yeah. it is complex :) for newbie

Answer 8

CityRn is the new name for a column I gave (City Record Number). I used ROW_NUMBER() function in order to order the items and assuming you want your columns to be named City1 City2 City3 and not just [1],[2],[3] I added 'City' in front of the number.

The topic of 'dynamic pivot' has been discussed many times in this forum.

The more complicated topic is a dynamic pivot on multiple columns - and this topic is not widely discussed, that's why my blog on this topic should be handy

Dynamic PIVOT on multiple columns

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

Naomi,

When I tried

;with cte as (select City, 'City' + cast(row_number() over (order by ID) as varchar(10)) as CityRec from testtmp)

select * from cte PIVOT (max(City) for CityRec IN ([Richmond],[LA],[Washington],[Denver])) pvt

I was getting incorrect sybtax near Select

And when I tried

declare @SQL nvarchar(max), @Cols nvarchar(max)

select @Cols = stuff((select ', ' + quotename(CityRn) from (select 'City' + 
cast(row_number() over (order by ID) as varchar(10)) as CityRn

from testtmp) X order by CityRn for XML PATH('')),1,2,'')

set @SQL = 'select * from (select City, ''City'' + 
cast(row_number() over (order by ID) as varchar(10)) as CityRn
FROM testtmp) src

PIVOT (max(City) FOR CityRn IN (' + @Cols + ')) pvt'

print @SQL -- test

execute (@SQL)
I get invalid column name ID....

---

Answer 10

I assumed that your table has ID column. If it doesn't, just use ORDER BY City then, e.g.

;with cte as (select City, 'City' + cast(row_number() over (order by City) as varchar(10)) as CityRec from testtmp)

select * from cte PIVOT (max(City) for CityRec IN ([City1],[City2],[City3],[City4])) pvt

--- Original corrected query--- I don't see errors


--------------------------------------------------

declare @SQL nvarchar(max), @Cols nvarchar(max)

select @Cols = stuff((select ', ' + quotename(CityRn) from (select 'City' + 

cast(row_number() over (order by City) as varchar(10)) as CityRn

from testtmp) X order by CityRn for XML PATH('')),1,2,'')

set @SQL = 'select * from (select City, ''City'' + 

cast(row_number() over (order by ID) as varchar(10)) as CityRn

FROM testtmp) src

PIVOT (max(City) FOR CityRn IN (' + @Cols + ')) pvt'

print @SQL -- test

execute (@SQL)

I corrected both queries - original static and dynamic.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Naomi,

Thanks.  yes, after I change ID to City it almost worked and after put ) after testtmp it works :)

By the way,  now the result looks like this.

   Seattle   LA  NY  Miami

1  Null      Null  Null  Null

is these a way to see only Seattle  LA  NY   Miami?

Thanks again.

Answer 12

I don't understand how did you get that result. Please post the exact queries you used. With my queries above you should not have gotten such result.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

oh...

;with cte as (select City, 'City' + cast(rownumber() over (order by City) as varchar(10)) as CityRec from testtmp)

select * from cte PIVOT (max(city) for CityRec in ([Seattle],[LA],[NY],[Miami]))

pvt

and i got results in SSMS...

  Seattle   LA  NY  Miami

1  Null      Null  Null  Null

Answer 14

This is not what I posted. The query I posted is

;with cte as (select City, 'City' + cast(rownumber() over (order by City) as varchar(10)) as CityRec from testtmp)

select * from cte PIVOT (max(city) for CityRec in ([City1],[City2],[City3],[City4]))

Comment the PIVOT part of the above query first (add -- before PIVOT) to understand that does CTE produce. Then you'll understand why your version produced the output you showed.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

Naomi,

Yes, you are right, I replaced City1-4 with actual name so my result looks like this

    City1   City2   City3   City4

1  LA       Seattle NY      Miami

Is there a way to hide column name?

Thanks

Answer 16

Where exactly do you want to present your result? 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 17

Naomi,

It's for HP PPM report and they'd like display that way instead of

City

LA

DC

NY

to;

City  LA  DC  NY

Thanks

Answer 18

The solution I posted solves this problem. You don't have to print column names.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 19

Naomi,

Thanks.  Is it set nocount on and off to hide column names?

Answer 20

No. In the client you need not show the column names. (In other words, in your reporting engine).

SET NOCOUNT ON suppresses system messages such as number of rows affected.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 21

Naomi,

Now I get it, duh...  silly me...

Thanks again.