mixing two columns in a query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

mixing two columns in a query

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-01-17 : 11:10:17

Location Name
-------------------------------
Loc1 Name1
Loc2 Name1
Loc3 Name2
Loc4 Name3

I have two columns titled Name and Description in a table called Buildings. I'd like to write a query that formats the above like this:

[Column Name Location]
----------------------
Name1
Loc1
Loc2
Name2
Loc3
Name3
Loc4

Is it possible to do something like this without writing a procedure?

thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 12:08:13

Try this(not tested):-

SELECT r.Caption AS [Column Name Location]
FROM
(
SELECT tmp1.Name as Caption,tmp1.NSerial as Order1,0 as Order2 
FROM
(SELECT Location,
        Name,
        (SELECT COUNT(*) + 1 from Table where Name <t.Name) AS NSerial,
(SELECT COUNT(*) + 1 from Table WHERE Name=t.Name AND Location<t.Location) AS LSerial
FROM Table t
)tmp1

UNION 

SELECT tmp2.Location as Caption,tmp2.NSerial as Order1,tmp2.LSerial as Order2 
FROM
(SELECT Location,
        Name,
        (SELECT COUNT(*) + 1 from Table where Name <t.Name) AS NSerial,
(SELECT COUNT(*) + 1 from Table WHERE Name=t.Name AND Location<t.Location) AS LSerial
FROM Table t
)tmp2
)r
ORDER BY r.Order1,r.Order2

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-01-17 : 15:22:50

Wow, it worked! Thank you. Now I will spend the next hour analyzing exactly how you figured this out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources