April 22, 2004 at 11:50 am
I need to read columns in first table and insert it as rows in second table. (Like a pivot table). How do I do that instead of using a cursor.
The result (second table) should look like
field1 from first table value of the field1
field2 from first table value of the field2
field3 from first table value of the field3
field4 from first table value of the field4
Any ideas!!! Appreciate your help.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 22, 2004 at 12:12 pm
Do you know the column names in advanced? Or does it need to be dynamic?
:wq
April 22, 2004 at 12:50 pm
I know the column names in advance.
I kind of made it working. Here is the query:
--TEMP TABLE TO HOLD COLUMNS AS ROWS
CREATE TABLE #SECOND_TABLE
(REVIEWID VARCHAR(10) NULL,
FIELD_NAME VARCHAR(20) NULL,
FIELD_VALUE DATETIME NULL)
GO
INSERT INTO #SECOND_TABLE (REVIEWID, FIELD_NAME, FIELD_VALUE)
SELECT REVIEWID,
FIELD_NAME = CASE WHEN FIRST_TABLE_FIELD1 IS NOT NULL THEN 'FIRST_TABLE_FIELD1' END,
FIELD_VALUE = CASE WHEN FIRST_TABLE_FIELD1 IS NOT NULL THEN FIRST_TABLE_FIELD1 END
FROM FIRST_TABLE
GROUP BY REVIEWID, FIRST_TABLE_FIELD1
UNION
SELECT REVIEWID,
FIELD_NAME = CASE WHEN FIRST_TABLE_FIELD2 IS NOT NULL THEN 'FIRST_TABLE_FIELD2' END,
FIELD_VALUE = CASE WHEN FIRST_TABLE_FIELD2 IS NOT NULL THEN FIRST_TABLE_FIELD2 END
FROM FIRST_TABLE
GROUP BY REVIEWID, FIRST_TABLE_FIELD2
go
SELECT * FROM #SECOND_TABLE
go
DROP TABLE #SECOND_TABLE
GO
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
April 22, 2004 at 1:01 pm
That looks like it would work.
:wq
April 23, 2004 at 7:19 am
Read this article carefully. It was very helfull to me.
http://www.winnetmag.com/Article/ArticleID/15608/15608.html
Mirko
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply