Display in one Row SQL Select

Hello,
can you please help,
What is the simplest way to get below to display inone row.

Select ZoneToID,UNitPrice from PricesZoneWeight
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51

This is how it looks now,
ZoneToID      UNitPrice
1852      0.26
1853      0.74
1854      0.82
1855      0.84
1856      0.88
1857      0.94

I would like to have it display as
ZoneToID      1852      1853      1854      1855      1856      1857
UNitPrice      0.26       0.74               0.82      0.84               0.88      0.94

I have about 150 ZoneToID

Your help is appreciated.
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
0
Najam UddinCommented:
User Pivot
SELECT <<ColumnNames>> 
FROM <<TableName>> 
PIVOT
 (
   AggregateFunction(<<ColumnToBeAggregated>>)
   FOR PivotColumn IN (<<PivotColumnValues>>)
 ) AS <<Alias>> 

Open in new window


and check this
0
Joseph KrauszCEOCommented:
Check this out. Provides 6 solutions how to do it. I am personally using solution 5 as it's the shortest, but it has some drawbacks (I don't remember on the spot what)
http://www.sqlmatters.com/Articles/Converting%20row%20values%20in%20a%20table%20to%20a%20single%20concatenated%20string.aspx

Regards

Joseph
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

W.E.BAuthor Commented:
Thank you guys,
I need some more help Please.
I got as far as
SELECT * FROM (Select ZoneToID,UNitPrice  
From PricesZoneWeight
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
Group BY ZoneToID,UNitPrice) as s
PIVOT (Max(UNitPrice)
FOR ZoneToID IN ([1852], [1853], [1854], [1855], [1856], [1857], [1858], [1859], [1860], [1861], [1862], [1863],[1864]))
piv ;

I'm having some difficulty with the FOR ZoneToID IN  (I have about 150 ZoneToID).
Any way I can do this without having to enter the (150 ZoneToID) Entry.

Thanks,
0
Najam UddinCommented:
Select them as comma separated value in variable and use variable

SELECT @zondeIds= COALESCE(@zondeIds, '') + ',' + zoneId
From table 
where .....

Open in new window


And for that you have to make this dynamic query
0
W.E.BAuthor Commented:
Can you please show me how?
I tried what you suggested,
SELECT @zondeIds= COALESCE(@zondeIds, '') + ',' + ZoneToID
From PricesZoneWeight
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51

I get error,
Must declare the scalar variable "@zondeIds".
0
Najam UddinCommented:
Okay, just tell if this executes okay, then I will update to include above query in it

SELECT * FROM (Select ZoneToID,UNitPrice  
From PricesZoneWeight 
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
Group BY ZoneToID,UNitPrice) as s
PIVOT (Max(UNitPrice)
FOR ZoneToID IN ([1852], [1853], [1854], [1855], [1856], [1857], [1858], [1859], [1860], [1861], [1862], [1863],[1864]))
piv ; 

Open in new window


And by way Declare @zondeIds varchar(8000) before

SELECT @zondeIds= COALESCE(@zondeIds, '') + ',' + ZoneToID
From PricesZoneWeight 
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51

Open in new window

0
W.E.BAuthor Commented:
Hi Najam, thank you for your time and help,

yes this works.
SELECT * FROM (Select ZoneToID,UNitPrice  
From PricesZoneWeight
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
Group BY ZoneToID,UNitPrice) as s
PIVOT (Max(UNitPrice)
FOR ZoneToID IN ([1852], [1853], [1854], [1855], [1856], [1857], [1858], [1859], [1860], [1861], [1862], [1863],[1864]))
piv ;
0
Najam UddinCommented:
try this, and if you face problem please attach create table script of PricesZoneWeight
Declare @zondeIds varchar(3000) 
SELECT @zondeIds= COALESCE(@zondeIds, '') + ',' + ZoneToID
From PricesZoneWeight 
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51

DECLARE @query NVARCHAR(4000)
SET @query = 'SELECT * FROM (Select ZoneToID,UNitPrice  
				From PricesZoneWeight 
				where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
				Group BY ZoneToID,UNitPrice) as s
				PIVOT (Max(UNitPrice)
				FOR ZoneToID IN ('+ @zondeIds + '))
				piv ;'

EXEC sp_executesql @query

Open in new window

0
W.E.BAuthor Commented:
I get error
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ',' to data type int.

on this ;line
SELECT @zondeIds= COALESCE(@zondeIds, '') + ',' + ZoneToID

I also noticed, you have quotes around this

'SELECT * FROM (Select ZoneToID,UNitPrice  
                        From PricesZoneWeight
                        where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
                        Group BY ZoneToID,UNitPrice) as s
                        PIVOT (Max(UNitPrice)
                        FOR ZoneToID IN ('+ @zondeIds + '))
                        piv ;'
0
Brian CroweDatabase AdministratorCommented:
Please take a look at some of the links that have been provided and educate yourself a little on what dynamic SQL is and sp_executesql.  Basically you are building a query as a string and passing it to a system procedure to execute.  That is what you are going to need to do to avoid manually coding every single column by hand especially since you don't necessarily know them all when you write the query.
0
Najam UddinCommented:
Declare @zondeIds varchar(3000) = NULL
SELECT @zondeIds = COALESCE(@zondeIds + ', ' ,'') + '['+CONVERT(VARCHAR(10),ZoneToID)+']' FROM PricesZoneWeight  
where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
SELECT @zondeIds

DECLARE @query NVARCHAR(4000)
SET @query = 'SELECT * FROM (Select ZoneToID,UNitPrice  
				From PricesZoneWeight 
				where PricePlanID = 5205 and ZoneFromID = 1852 and WeightFrom =51
				Group BY ZoneToID,UNitPrice) as s
				PIVOT (Max(UNitPrice)
				FOR ZoneToID IN ('+ @zondeIds + '))
				piv ;'

EXEC sp_executesql @query

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
AWESOME,
Thank you very much.

This is the Best way to learn,  
Now I know how the Dynamic Pivot works..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.