Question on aliasing a column based on a value

[chrisdoesstuff]

I am trying to write a query that will end up on a crystal report. SQL 2005 and this will be part of a stored procedure.

I have a basically 1 table that contains two rows which have all my information in them but I need to be able to alias the column based on the values contained in a way where they each come out in their own column (field).

Example mytable.findingname, mytable.findingvalue

I have 125 values in this table that need to become 125 separate items for formatting reasons. There is a web based application that is formatted a certain way and the report needs to mimic it.

So instead of findingvalue I would like
findingvalue1 findingvalue1a findingvalue1b findingvalue1c
findingvalue2 findingvalue2a findingvalue2b findingvalue2c
returned in a single row from my query.

findingvalue1 = 'value_abc'
findingvalue1a ='value_abca
findingvalue1b= 'value_abcb'

The 125 values are preset and already known. Temp tables/ table variables are fine. I'll take any feedback you can provide.

 

[gmmastros]

Since you are using SQL2005, I would encourage you to do a little research on the unpivot command.

[google]SQL Server Unpivot Example[/google]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try

select * from myTable PIVOT (max(FindingValue) FOR FindingName in
([Name1],[Name2], etc. )) pvt

You can alternatively do this using dynamic PIVOT...

PluralSight Learning Library

 

[chrisdoesstuff]

Thanks for the input. I'm trying to wrap my head around Pivot.

Markros I'm going to take a shot with your code and I'll let you know what happens soon. Thank you again for your help.

 

[chrisdoesstuff]

Thanks for the help unfortunately whoever upgraded this db from 2000 to 2005 did not upgrade the compatibility so I am getting an error.

Is there an reasonably elegant way to pull this off in SQL2000?

 

[gmmastros]

You can. Like this:

Declare @Temp Table(Id Int, FindingName VarChar(20), FindingValue VarChar(20))

Insert Into @Temp Values(1,'Name','Apple')
Insert Into @Temp Values(1,'Color','Red')
Insert Into @Temp Values(1,'Size','2 inches')
Insert Into @Temp Values(1,'Cost','$0.50')

Insert Into @Temp Values(2,'Name','Banana')
Insert Into @Temp Values(2,'Color','Yellow')
Insert Into @Temp Values(2,'Size','4 inches')
Insert Into @Temp Values(2,'Cost','$0.27')

Select ID,
       Min(Case When FindingName = 'Name'  Then FindingValue End) As Name,
       Min(Case When FindingName = 'Color' Then FindingValue End) As Color,
       Min(Case When FindingName = 'Size'  Then FindingValue End) As Size,
       Min(Case When FindingName = 'Cost'  Then FindingValue End) As Cost
From   @Temp
Group By Id

The code above creates a table variable and hard codes some data. This means you can copy/paste the code above in to a query window and run it to see how it works. Once you understand how it works, it should be simple to apply it to your table.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Talk with your DBA about compatibility level. I find that it's much better to keep database at the compatibility level that corresponds to the SQL Server version.

I recently found a same problem on the database I was testing performance with. Once I changed the compatibility level to match SQL Server version, my tests started to behave differently, so the compatibility level of the database does affect many things.

PluralSight Learning Library