Multiple columns - 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 2008 Forums

Transact-SQL (2008)

Multiple columns

Author

Topic

_PJ_
Starting Member

3 Posts

Posted - 2012-05-13 : 12:11:51

Hey

So, how can i convert this select result example (from one table):

CID Answer
1 Good
1 BAD
1 True
1 False

to this:

CID Answer1 Answer2 Answer3 Answer 4
1 Good Bad True False

Each id will have 40 answers

Thank you all

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-13 : 13:02:04

You can use the PIVOT operator like this, or you can use dynamic pivot as explained in Madhivana's blog

SELECT
	CID,
	[Good] AS Answer1,
	[BAD] AS Answer2,
	[True] AS Answer3,
	[False] AS Answer4
FROM
	YourTable
PIVOT
(MAX(Answer) FOR Answer IN ([Good],[Bad],[True],[False]))P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:20:33

quote:
Originally posted by _PJ_

Hey

So, how can i convert this select result example (from one table):

CID Answer
1 Good
1 BAD
1 True
1 False

to this:

CID Answer1 Answer2 Answer3 Answer 4
1 Good Bad True False

Each id will have 40 answers

Thank you all

you want all 40 as separate or just always 4 of them? if latter, whats the condition based on which you want 4?

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

_PJ_
Starting Member

3 Posts

Posted - 2012-05-13 : 15:32:08

I will always want to put in each "row", the 40 answers given by someone!

That with just 4 elements was just an example of the result that i'm looking for.

I thought to create a temporary table but probably there is a better way to accomplish this

quote:
Originally posted by visakh16

quote:
Originally posted by _PJ_

Hey

So, how can i convert this select result example (from one table):

CID Answer
1 Good
1 BAD
1 True
1 False

to this:

CID Answer1 Answer2 Answer3 Answer 4
1 Good Bad True False

Each id will have 40 answers

Thank you all

you want all 40 as separate or just always 4 of them? if latter, whats the condition based on which you want 4?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 15:35:22

quote:
Originally posted by _PJ_

I will always want to put in each "row", the 40 answers given by someone!

That was just an example of the result that i, looking for.

I thought to create a temporary table but probably there is a better way to accomplish this

quote:
Originally posted by visakh16

quote:
Originally posted by _PJ_

Hey

So, how can i convert this select result example (from one table):

CID Answer
1 Good
1 BAD
1 True
1 False

to this:

CID Answer1 Answer2 Answer3 Answer 4
1 Good Bad True False

Each id will have 40 answers

Thank you all

you want all 40 as separate or just always 4 of them? if latter, whats the condition based on which you want 4?

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

thats fine
but in each row you want all of them in same column or separate columns?

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

_PJ_
Starting Member

3 Posts

Posted - 2012-05-13 : 15:40:22

Separate

quote:
Originally posted by visakh16

quote:
Originally posted by _PJ_

I will always want to put in each "row", the 40 answers given by someone!

That was just an example of the result that i, looking for.

I thought to create a temporary table but probably there is a better way to accomplish this

quote:
Originally posted by visakh16

quote:
Originally posted by _PJ_

Hey

So, how can i convert this select result example (from one table):

CID Answer
1 Good
1 BAD
1 True
1 False

to this:

CID Answer1 Answer2 Answer3 Answer 4
1 Good Bad True False

Each id will have 40 answers

Thank you all

you want all 40 as separate or just always 4 of them? if latter, whats the condition based on which you want 4?

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

thats fine
but in each row you want all of them in same column or separate columns?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 16:47:08

then you should be using the method posted in Madhivanans blog as suggested by Sunita

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources