Column - A Rows by Any Other Name...

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

Transact-SQL (2000)

Column - A Rows by Any Other Name...

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 15:20:52

Sorry 'bout the hokey title , but I needed an attention grabber.

I have data that looks like this:

Who X M T W R F S

Xerxes Y N N N Y Y N
Ptolemy N N N Y N N N
Seleucus Y N N N N N N
Craterus N N N N N N Y
Clovis Y N Y N N N N

I want it to read like this:

Who When

Xerxes X
Xerxes R
Xerxes F
Ptolemy W
Seleucus X
Craterus S
Clovis X
Clovis T

I keep gravitating toward a cursor

(yikes!)

Any simple solution?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-29 : 15:45:10

A UNION ALL would be the easy way to go.


select [WHO], [WHEN] = 'X' from hockey where [X] = 'Y' union all
select [WHO], [WHEN] = 'M' from hockey where [M] = 'Y' union all
.. and so on

Edit: added the FROM clause

CODO ERGO SUM

eralper
Yak Posting Veteran

66 Posts

Posted - 2005-06-29 : 15:46:59

Hi,

I like hockey

I hope the below code works fine.

I assume Hockey is the source table name

select list.* from hockey
inner join (
select who as who, 'X' as [When] from hockey where x = 'Y'
union all
select who as who, 'M' as [When] from hockey where m = 'Y'
union all
select who as who, 'T' as [When] from hockey where t = 'Y'
union all
select who as who, 'W' as [When] from hockey where w = 'Y'
union all
select who as who, 'R' as [When] from hockey where r = 'Y'
union all
select who as who, 'F' as [When] from hockey where f = 'Y'
union all
select who as who, 'S' as [When] from hockey where s = 'Y'
) list on hockey.who = list.who

Eralper
http://www.kodyaz.com

-------------
Eralper
http://www.kodyaz.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-29 : 15:49:57

quote:
Originally posted by eralper

Hi,

I like hockey

LOL!

he said 'Hokey'.... not 'Hockey'

anyway, why did you add the outer select? A series of unions (Union All) would be just fine.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-29 : 16:12:16

How about a table re-design?
create table HokeyPokey (who varchar(10), X char(1), WeekDay char(1))

where you been Xerxes? I thought maybe you RE-UPed or something...

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 16:13:24

How did HOKEY

become HOCKEY

? You guys are a riot!

Thanks for the info and the laughs!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 16:14:59

TG....my job 'expanded when my boss left....they gave all the extra work to moi! Thanks for the help!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 16:25:07

Uh, one more thing wouldn't a cursor be great for this?. I gave a really simple version of the data. The WHO has a bunch of other info attached to it.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

eralper
Yak Posting Veteran

66 Posts

Posted - 2005-06-29 : 16:28:50

Hi all,

I added the outer select just to get the list in order.

The teams are listed according to the hockey

table and days of the same team are listed together

-------------
Eralper
http://www.kodyaz.com

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 17:36:50

Now what if I wanted the new order that's been created saved into a table?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-29 : 18:13:32

insert into table1 (...)
queryGoesHere

Is that what you are asking?

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 18:16:16

quote:
Originally posted by tduggan

insert into table1 (...)
queryGoesHere

Is that what you are asking?

Tara

YES, Tara!!

I can't believe I didn't see that. Yes, this is EXACTLY what I was looking for! You have a GREAT day!!!

BUT....the table has to be already defined.....right? I mean isn't there a way to do this and create the table on the fly like in "SELECT * INTO TABLE_Y FROM TABLE_X" ????

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-29 : 18:35:10

SELECT *
INTO TABLE_Y
FROM
(
queryGoesHere
) t

That doesn't copy the constraints or indexes over, just the base table structure.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 18:39:21

quote:
Originally posted by tduggan

SELECT *
INTO TABLE_Y
FROM
(
queryGoesHere
) t

That doesn't copy the constraints or indexes over, just the base table structure.

Tara

I tried that, but I keep getting errors on my syntax....

SELECT * FROM [Fowler].[dbo].[C2R]
GO

SELECT * INTO [Fowler].[dbo].[C2R2] --<<<<the new table
FROM (
select who as who, 'X' as [When] from C2R where x = 'Y'
union all
select who as who, 'M' as [When] from C2R where m = 'Y'
union all
select who as who, 'T' as [When] from C2R where t = 'Y'
union all
select who as who, 'W' as [When] from C2R where w = 'Y'
union all
select who as who, 'R' as [When] from C2R where r = 'Y'
union all
select who as who, 'F' as [When] from C2R where f = 'Y'
union all
select who as who, 'S' as [When] from C2R where s = 'Y'
) <---error comes here...

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-29 : 18:42:37

You left off the t that I had in the query. It wasn't a typo. Check my query again.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 18:51:17

quote:
Originally posted by tduggan

You left off the t that I had in the query. It wasn't a typo. Check my query again.

Tara

Ooops, I honestly thought the 't' was a cutesy signature (for Tara)....forgive my boneheadedness (I'm 48)... I'll try that now!

And...it works!! Could you explain just what it was the "t" does?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-29 : 18:53:17

It's an alias. Since the query is a derived table now, SQL Server needs to have a name for it.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 18:57:42

quote:
Originally posted by tduggan

It's an alias. Since the query is a derived table now, SQL Server needs to have a name for it.

Tara

Thanks for the lesson. I most sincerely appreciate your assistance!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-29 : 19:02:14

You sure are getting a lot of mileage out of a question I thought I answered on the first reply.

Did you just miss us, and now you're dragging this whole thing out because you enjoy our company?

Well, nice to see you back.

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-06-29 : 19:04:08

quote:
Originally posted by Michael Valentine Jones

You sure are getting a lot of mileage out of a question I thought I answered on the first reply.

Did you just miss us, and now you're dragging this whole thing out because you enjoy our company? Well, nice to see you back.

CODO ERGO SUM

Actually you're right on both counts, Col. Jones!! Thanks for the welcome. Nice to be back! I missed you guys!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-29 : 19:45:25

Do you not post often because you feel obligated to come up with really bad puns for your thread titles and it's just too much work?

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources