Question on creating aliased values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question on creating aliased values

Author

Topic

Chrisdoesstuff
Starting Member

2 Posts

Posted - 2011-07-26 : 08:53:46

SQL server 2008. I have a database which stores data for an application in the format observation.abbr (for the abbreviation which is distinct) and then the observation.value which stores the user entered values for each abbreviation. What I need to do is to select an abbreviation and make that it's own column so that I can have each presented.

So far I just wrote a bunch of select statements like

Insert into #test 2(abbr1, value1)
SELECT
abbr1 = o.abbr,
value1 = o.value
FROM observation o
WHERE o.abbr = 'abc123'

Then I just select from the temp table but that's not working because the value end up in a new row each time so I end up with a series of null values.

I tried subqueries also which will probably work but they are fairly slow. I have looked into Common Table Expressions but I'll admit that I really don't seem to be understanding them and while I can reproduce the examples I don't seem to require the same complexity that the examples show and I'm not getting how to break it down. I also tried to PIVOT but without an aggregation I'm not able to make that work.

So sorry for the wall of text but I hope that gives a good example of what I'm doing and trying to do. All I want to be able to do is alias the rows into individual columns rapidly. So when I have 20 different abbreviations I can query then and display them all in a single row. I'm sure this is common and fairly easy but I'm missing it. I do need to learn how this functions as it's something I want to use all the time.

Lastly- I can't make any changes to the db itself (indexes, permanent tables etc)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 09:55:34

I've read this post 3 times and still don't know exactly what you want.
Can you please give example data and the wanted output in relation to the sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Chrisdoesstuff
Starting Member

2 Posts

Posted - 2011-07-26 : 10:08:13

quote:
Originally posted by webfred

I've read this post 3 times and still don't know exactly what you want.
Can you please give example data and the wanted output in relation to the sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Sorry for the confusion-

I have a table that has the values

Abbr Value
Abc123 Thing1
Abc122 Thing2
Abc121 Thing3
Abc120 Thing4

I want to have
Abc123 Abc122 Abc121 Abc120
Thing1 Thing2 Thing3 Thing4

I see how I could just pivot it but when I look at examples they are using AVG or COUNT or another function which I don't need.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 11:41:58

you can use MAX instead here for crosstabing like below


SELECT [Abc123],[Abc122],[Abc121],[Abc120]
FROM Table t
PIVOT (MAX(Value) FOR Abbr IN ([Abc123],[Abc122],[Abc121],[Abc120]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources