Transforming one row into multiple rows

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)

Transforming one row into multiple rows

Author

Topic

zicosql
Starting Member

5 Posts

Posted - 2008-01-02 : 07:33:57

Hi,

I'm trying to see if there's a better way to tranform one row into multiple rows depending on the number of fields being used. At the moment I'm using dynamic sql and a cursor to achieve this and it works but its painfully slow. I'm using SQL 2005.

The table looks like the following and has a SUBCOUNT column to indicate how many of the sub fields are being used.

SUBID
SUBCOUNT
SUB1FORENAME
SUB1SURNAME
SUB2FORENAME
SUB2SURNAME
SUB3FORENAME
SUB3SURNAME
SUB4FORENAME
SUB4SURNAME
..
..
SUB20FORENAME
SUB20SURNAME

I could have 20 sub names per row. The data looks like this:


SUBID	SUBCOUNT	SUB1FORENAME	SUB1SURNAME	SUB2FORENAME	SUB2SURNAME	SUB3FORENAME	SUB3SURNAME
1	2		ALAN		SMITH		DANIEL		JONES		<NULL>		<NULL>
2	1		AMY		GREEN		<NULL>		<NULL>		<NULL>		<NULL>
3	0		<NULL>		<NULL>		<NULL>		<NULL>		<NULL>		<NULL>
4	3		PAUL		JONES		JULIE		JONES		PATRICK		JONES

I want to transform this into one row per sub person into the following structure.

ID
SUBID
FORENAME
SURNAME

So the data will look like this for the above example.


ID	SUBID		FORENAME	SURNAME
1	1		ALAN		SMITH
2	1		DANIEL		JONES
3	2		AMY		GREEN
4	4		PAUL		JONES
5	4		JULIE		JONES
6	4		PATRICK		JONES

At the moment using dynamic sql this takes 6 separate inserts using a cursor. When I have to do 30,000 inserts this takes 12 minutes even with proper indexing on the source table. Bare in mind that I have a lot of information so the above is just a sample of what I have to process.

Is there a way to avoid using a cursor and/or dyanmic sql bearing in mind the column names are different for each subrecord?

Any help would be appreciated.

Thanks.

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-02 : 08:03:19

Normalise your table design!
www.r937.com/relational.html

George
<3Engaged!

zicosql
Starting Member

5 Posts

Posted - 2008-01-02 : 08:17:33

The data is supplied to us in a denormalized state - I know all about normalization so lessons on how to do normalization arent useful.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-01-02 : 09:12:46

Try something like:

SELECT ROW_NUMBER() OVER(ORDER BY T.SubID, N.N) AS [ID]
    ,t.SubID
    ,CASE N.N
        WHEN 1 THEN Sub1Forename
        WHEN 2 THEN Sub2Forename
        ...
        WHEN 19 THEN Sub19Forename
        WHEN 20 THEN Sub20Forename
    END AS Forename
    ,CASE N.N
        WHEN 1 THEN Sub1Surname
        WHEN 2 THEN Sub2Surname
        ...
        WHEN 19 THEN Sub19Surname
        WHEN 20 THEN Sub20Surname
    END AS Surname
FROM YourTable T
    JOIN
    (
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        ...
        SELECT 19 UNION ALL
        SELECT 20
    ) N (N) -- or use a number/tally table
        ON N.N <= T.SubCount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 09:28:35

[code]SELECT ROW_NUMBER() OVER (ORDER BY tmp.SUBID,tmp.FORENAME,tmp.SURNAME
) AS 'ID',
tmp.SUBID,
tmp.FORENAME,
tmp.SURNAME
FROM
(
SELECT SUBID,SUB1FORENAME AS 'FORENAME',SUB1SURNAME AS 'SURNAME'

UNION ALL

SELECT SUBID,SUB2FORENAME AS 'FORENAME',SUB2SURNAME AS 'SURNAME'

UNION ALL
...

SELECT SUBID,SUB20FORENAME AS 'FORENAME',SUB20SURNAME AS 'SURNAME'
) tmp[/code]
WHERE tmp.SURNAME IS NOT NULL
AND tmp.FORENAME IS NOT NULL

zicosql
Starting Member

5 Posts

Posted - 2008-01-02 : 09:33:51

Thanks to both Ifor and visakh16 - that helped put me on the right track!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources