CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlrelational-database
user3362735
user3362735

Reputation:

Converting Certain Columns into Row Data

There is a table which was unfortunately designed in the following format (going up to 39 instead of 3):

TABLE: Joined_Table

Key        Nm_1    Nm_2     Nm_3    Age1 Age2  Age3 State1  State2  State3
1          Bob     Sally    John    12   13    14   Fl.     AL.     NY. 
2          Jack    Billy    Jean    15   15    83   AL.     TX.     WA.

Before beginning a SharePoint migration, I need to single out these items rows into individual items. I need the copy the data to another table in a similar to this format:

TABLE: dbo.Line_Items

K   FK  Name_T  Age_T  State_T
1   1   Bob     12     Fl.
2   1   Sally   13     AL
3   1   John    14     NY
4   2   Jack    15     AL
5   2   Billy   15     TX
6   2   Jean    83     WA

Is was told to consider using "Cross Apply", and I plan on doing that but am wondering if anyone else has experience or a suggestion on a clean way to do this (remembering this goes up to 39). I don't need something extremely dynamic as I wouldn't mind having to type out each column type 39 times. For example, I was considering creating the table and then inserting a row on Nm_1, Age1, State1 for every row and then replacing 1 with 2, etc. But even with this method, I'm not sure how to create a new row on Nm_1, Age1, and State1 for all instances of rows in the original table.

Upvotes: 0

Views: 53

Answers (2)

user3362735
user3362735

Reputation:

I know this is hardly the cleanest or most efficient, but it worked for my simple case. I used a dynamic string to store the SQL statement and a while loop to iterate through each of the 39 rows:

DECLARE @MyQuery nvarchar(MAX)
DECLARE @MyInt int

SET @MyInt = 1

WHILE @MyInt <= 39

BEGIN

SET @MyQuery = 'INSERT INTO dbo.[Line_Items]

(FK, Name_T, Age_T, State_T)

SELECT [Key], [Nm_1], [Age1], [State1]

FROM dbo.Joined_Table

WHERE LTRIM(RTRIM(Nm_1)) NOT LIKE ''''

ORDER BY ID
'

SET @MyQuery = REPLACE(@MyQuery,1,@MyInt)
SET @MyInt = @MyInt+1

EXEC(@MyQuery)

    END

`

Upvotes: 0

T I
T I

Reputation: 9933

Cross apply is a pretty clean solution

select 
    key = row_number() over(order by fk, k),
    fk,
    name_t,
    age_t,
    state_t
from tbl 
cross apply (
    values
        (1, key, nm_1, age1, state1),
        (2, key, nm_2, age2, state2),
        -- ...
        (39, key, nm_39, age39, state39)
) c (k, fk, name_t, age_t, state_t)

Upvotes: 1

Related Questions