Newbie question: how can I convert values from rows into columns

Question

Post reply

Newbie question: how can I convert values from rows into columns

Ana Cardoso

Grasshopper

Points: 12
More actions
May 15, 2006 at 11:12 am

#103162

Hi,
I'm new to Sql Server and I can´t find a solution for this problem:
I have a table like this:
Id Value
1 a
2 a
3 b
2 c
1 x
1 a
(Id is uniqueidentifier and Value is a varchar(50)).
I need to get a new table with this result:
Id Value1 Value2 Value3
1 a x a
2 a c null
3 b null null
Thanks for any help.
Best regards,
Ana Cardoso

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

I do not think it is possible in 1 query. You will need a loop or temp table / variable to solve this

Kindest Regards,

Amit Lohia

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

If you're trying to do it the way you describe that could be problemmatic. If you want to pivot data and you're using SQL Server 2005, you want to look at the PIVOT function in BOL. If you're using 2000, there's several good examples for how to pivot data using the CASE expression in SQL. You can probably find them using google. If you have access to it, Ken Henderson has an excellent example in his book "The Guru's Guide to Transact-SQL" (which is a phenomenal book to own anyway).

noeld SSC Guru Points: 96590 More actions · Answer 3

There are several posibilities and you will need to choose acording to the complexity you need to handle. For a quick solution have a look at:

sp_crosstab

My advice is to see if you can have a limit in the number of columns so that no dynamic sql is used.

Cheers,

* Noel

RLSQL Say Hey Kid Points: 713 More actions · Answer 4

Hi Ana,

I hope you will understand the following code, and adapt this approach to your problem. Take some time to review it, you may find it useful for other purposes.

Warning: This code doesn't handle ties properly. Execute the inserts for Eric, and you'll see what happens.

Here is the code:

Set NoCount On

go

Create Table TestData (Name Varchar(50) Not Null, SalesAmount Int Not Null)

go

Insert Into TestData (Name, SalesAmount) Values ('Andrew', 100)

Insert Into TestData (Name, SalesAmount) Values ('Brian', 200)

Insert Into TestData (Name, SalesAmount) Values ('Brian', 300)

Insert Into TestData (Name, SalesAmount) Values ('Chris', 400)

Insert Into TestData (Name, SalesAmount) Values ('Chris', 500)

Insert Into TestData (Name, SalesAmount) Values ('Chris', 600)

Insert Into TestData (Name, SalesAmount) Values ('David', 700)

Insert Into TestData (Name, SalesAmount) Values ('David', 800)

Insert Into TestData (Name, SalesAmount) Values ('David', 900)

Insert Into TestData (Name, SalesAmount) Values ('David', 1000)

/*

Insert Into TestData (Name, SalesAmount) Values ('Eric', 1100)

Insert Into TestData (Name, SalesAmount) Values ('Eric', 1200)

Insert Into TestData (Name, SalesAmount) Values ('Eric', 1300)

Insert Into TestData (Name, SalesAmount) Values ('Eric', 1400)

*/

go

Create View SalesRankings

As

Select

Name

, SalesAmount

, (

Select

Count(0) + 1

From

TestData TDS

Where

TDS.Name = TD.Name

And TDS.SalesAmount > TD.SalesAmount

) Ranking

From

TestData TD

go

Create View SalesTop3

As

Select

SR1.Name

, SR1.SalesAmount First

, SR2.SalesAmount Second

, SR3.SalesAmount Third

From

SalesRankings SR1

Left Outer Join SalesRankings SR2 On SR1.Name = SR2.Name And SR2.Ranking = 2

Left Outer Join SalesRankings SR3 On SR3.Name = SR1.Name And SR3.Ranking = 3

Where

SR1.Ranking = 1

go

Select * From SalesTop3

Richard

Ana Cardoso Grasshopper Points: 12 More actions · Answer 5

Thanks ALL for great help!!!

I tried Richard's solution and it worked just fine.

Best regards,

Ana Cardoso