|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Splitting a single row into multiple rows
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Splitting a single row into multiple rows
|
|
| Angela 2002-11-07, 7:23 am |
| Hi there,
I am trying to transfer data from one table into a new format table.
The data needs to come from a table like:
Q1 Q2 Q3
--- --- ---
5 5 8
And needs to be transformed into this format
QuNo Result
---- ----
Q1 5
Q2 5
Q3 8
Does anyone have SQL statement that can do this?
Thanks in advance,
Angela
| |
| Dan Guzman 2002-11-07, 8:23 am |
| Try UNION ALL:
CREATE TABLE DenormalizedTable
(
Q1 int NOT NULL,
Q2 int NOT NULL,
Q3 int NOT NULL
)
CREATE TABLE NormalizedTable
(
QuNo CHAR(2) NOT NULL,
Result int NOT NULL
)
GO
INSERT INTO DenormalizedTable VALUES(5, 5, 8)
GO
INSERT INTO NormalizedTable
SELECT
'Q1' AS QuNo,
Q1 AS Result
FROM
DenormalizedTable
UNION ALL
SELECT
'Q2',
Q2
FROM
DenormalizedTable
UNION ALL
SELECT
'Q3',
Q3
FROM
DenormalizedTable
GO
SELECT * FROM NormalizedTable
ORDER BY QuNo
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Angela" <angela@kaisen.co.uk> wrote in message
news:6f2d6293.0211070455.743a23e0@posting.google.com...
> Hi there,
>
> I am trying to transfer data from one table into a new format table.
>
> The data needs to come from a table like:
>
> Q1 Q2 Q3
> --- --- ---
> 5 5 8
>
>
> And needs to be transformed into this format
>
> QuNo Result
> ---- ----
> Q1 5
>
> Q2 5
>
> Q3 8
>
> Does anyone have SQL statement that can do this?
>
> Thanks in advance,
> Angela
|
|
|
|
|