how to show row wise recode to column wise

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 2005 Forums

Transact-SQL (2005)

how to show row wise recode to column wise

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2008-02-16 : 05:12:02

Hi All,
i am using sql server 2005.
in a table a record is entered as product1 , status1 ,product2 , status2 , product3 , status3 in a row now i have to display it as
product1 status1
product2 status2
product3 status3

how can i do it .

please help.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-16 : 06:46:48

One way is:
CREATE TABLE #temp1
(ID INT,
product1 INT,
status1 BIT,
product2 INT,
status2 BIT,
product3 INT,
status3 BIT
)

INSERT INTO #temp1
SELECT 1,1001,1,1002,0,1003,1
UNION
SELECT 2,2001,1,2002,0,2003,1
UNION
SELECT 3,3001,1,3002,0,3003,1
UNION
SELECT 4,4001,1,4002,0,4003,1
UNION
SELECT 5,5001,1,5002,0,5003,1

SELECT product1,status1 FROM #temp1
UNION
SELECT product2,status2 FROM #temp1
UNION
SELECT product3,status3 FROM #temp1

DROP TABLE #temp1

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-17 : 08:59:03

If you are sure the data from productx,statusx sets dont contain any duplicates you can use UNION ALL for joining the subsections as UNION ALL wont try to take distinct values and performs better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources