Insert Column value to Row - 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

General SQL Server Forums

New to SQL Server Programming

Insert Column value to Row

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-01-05 : 07:27:53

Hi Guys!

I have truble in sql server 2005 that is I have table called
Test (Day) containts value sunday, monday, Friday

and another table
Test1 (WoSun,WoMon,WoTue,WoWed,WoThu,WoFri,WoSat) with no value

I need to insert value 1 or 0 to Test1 if I have concern value in Test
Like if Sunday than insert 1 into WoSun?????

How can I do this? Please Help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 08:05:36

Based only on what you described, you can do the following:

INSERT INTO Test1
(WoSun,WoMon,WoTue,WoWed,WoThu,WoFri,WoSat)
SELECT
	MAX(CASE WHEN [Day] = 'Sunday' THEN 1 ELSE 0 END) AS WoSun,
	MAX(CASE WHEN [Day] = 'Sunday' THEN 1 ELSE 0 END) AS WoSun,
	-- and so on for the other 5 days
FROM
	Test

But I suspect there is more to it. Don't you have other columns in the table such as an ID or key that relate the two tables? If you do, you will need to join on those as well, or use PIVOT operator.

If you can post more details, I am sure people on the forum would be able to offer more concrete suggestions. Take a look at Brett's blog for help in posting: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 09:33:05

Why do you need a table for this? looks like what you need is set of variables as its always one row. What are you using the row for by the way?

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

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-01-06 : 01:13:10

Thanks sunita the aggregate MAX () worked for me as I was jumbling around case........ Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources