Converting Row to Column?
-
Hello Everyone!
I m new to SQL Central. This is my first post too.
I have a problem and need an urgent solution.
I have data like below:
[
ID Order1 Order2 Order3 ....
---- ------ ------ ------
1 34 44 55 ....
2 66 77 88 ....
.
.
]
I want it like:
ID Orders
--- ------
1 34
1 44
1 55
. .
. .
. .
2 66
2 77
2 88
. .
. .
. .
and so on:
Thanx in Advance..
JAN
-
Hi Jan, I am not quite sure what you are looking for. Are you selecting data from more than one table and if so what is the relationship between tables. From the informaiton you have given the following will work.
SELECT Orders.ID, Orders.OrderQuanity
FROM Orders
Order By ID;
HTH
Mike
-
SET NOCOUNT ON
CREATE TABLE #notnice
(
id INT IDENTITY PRIMARY KEY
, ORDER1 INT
, ORDER2 INT
, ORDER3 INT
)
CREATE TABLE #better
(
id INT
, [ORDER] INT
)
INSERT INTO #notnice VALUES(34,44,55)
INSERT INTO #notnice VALUES(340,440,550)
INSERT INTO #better (id, [Order]) SELECT id, ORDER1 FROM #notnice
INSERT INTO #better (id, [Order]) SELECT id, ORDER2 FROM #notnice
INSERT INTO #better (id, [Order]) SELECT id, ORDER3 FROM #notnice
SELECT * FROM #better
DROP TABLE #notnice, #better
SET NOCOUNT OFF
id ORDER
----------- -----------
1 34
2 340
1 44
2 440
1 55
2 550
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
select id,order1 from od union select id,order2 from od union select id,order3 from od
My Blog:
http://dineshasanka.spaces.live.com/ -
Thanx to all of U for the great support.
I have tried union but not working for many columns.
The idea of inserting the ID, Orders in a new table [#better] is the better option, posted by (Frank Kalis)..
I don't have just order 1 to 3 but it is like about 100. Case is not just about Orders, it is something else, but I simplified it for ease.
I am again thankful to all of you. Hoping to get more help from you people.
With Best Wishes and Regards.
JAN
-
Jan,
sorry UNION will not if you have 100 columns. even UNION will slow doen the things.
In that case Frank method is ok
Sorry about it!
My Blog:
http://dineshasanka.spaces.live.com/ -
Jan, you know that such a table structure violates 1 Normalform? If you have the chance to get rid of this structure, go for it and normalize the structure.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply