asked on

Can you please help in below sql server query

Hello,
I have data like below in a sql server table

NY Albany
NY NYC
NY Rochester
NY Buffalo
Nj Princeton
Nj Trenton
Ct Milford
Ct Hartford
Ct Stanford

I want output as below

Ny Nj ct
Albany princeton milford
NYC Trenton hartford
Rochester stanford
Buffalo

Can any one please help . I want a dynamic script for this. there can be n' number of states

Thanks

ste5an

See Dynamic Pivot Procedure for SQL Server.

But having n elements does not necessarily means you need dynamic SQL. In your case n is limited and countable. When I need to guess it's about 50 or so ;)

vijay11

ASKER

Hello I tried with above example but I could not get what I wanted. I have many states from different countries. Can some one please give the SQL if possible

PortletPaul

>>"I tried with above example but I could not get what I wanted"

Post that attempt and any errors it produced and we can help you. This way you learn.

vijay11

ASKER

i am getting results like

Ny Nj Ct
Ny Nj Ct
Ny Nj Ct
.
.
It is repeated 9 times

PortletPaul

post your query please

vijay11

ASKER

I work in a financial client . sites don't open in office. I am typing this from home. Sorry

vijay11

ASKER

May be some thing like this.

select ny,nj,ct
from (select state,city from table)
pivot
max(state) in [ny,nj,ct)

PortletPaul

{this comment has been edited}

How are you presenting this information? e.g. in HTML
You may find it easier to achieve using that FINAL "presentation layer" instead of SQL

This pivot query may assist:

SELECT 
     rn, [NJ], [NY], [CT]
FROM (
    select [city],[state], row_number() over(partition by State order by City ASC) as rn
    from table1
    ) p
PIVOT (
      max(city)
      FOR [state] IN ( [NJ], [NY], [CT] )
      ) AS pvt
ORDER BY rn

Open in new window

the result looks like this:

| rn |        NJ |        NY |       CT |
|----|-----------|-----------|----------|
|  1 | Princeton |    Albany | Hartford |
|  2 |   Trenton |   Buffalo |  Milford |
|  3 |    (null) |       NYC | Stanford |
|  4 |    (null) | Rochester |   (null) |

Open in new window

Note I am using ROW_NUMBER() to generate something that you can "pivot around". You can suppress output of that column if needed.

vijay11

ASKER

i have to present this information in excel to user

vijay11

ASKER

Thanks. if I have many states . Can we do this dynamically?

PortletPaul

I changed my previous comment, sorry, you may have read it before the edit was made.

PortletPaul

Yes it can be dynamic.