CODEWITHSUNDEEP
sqlsql-server-2008
codingManiac
codingManiac

Reputation: 1700

INSERT record for each unique column value

I have a table with records such as...

FileName     |   A1   |   A2   |   A3   |
TwinCity.fi     233.3   235.33   379.8
CountryVil.fi   37.8             74.58

And I need to insert a record into another table for each filename and unique A value so for the above I would insert...

TwinCity.fi    233.3
TwinCity.fi    235.33
TwinCity.fi    379.8
CountryVil.fi  37.8
CountryVil.fi  74.58

I know what I need to do but how can I do this?

Upvotes: 0

Views: 65

Answers (2)

Jodrell
Jodrell

Reputation: 35696

How about, Fiddle Here

INSERT [TableTo]
SELECT * FROM
(
    SELECT [FileName], [A1] [Value] FROM [TableFrom]
    UNION
    SELECT [FileName], [A2] [Value] FROM [TableFrom]
    UNION
    SELECT [FileName], [A3] [Value] FROM [TableFrom]
) [M]
WHERE [M].[Value] IS NOT NULL;

Upvotes: 1

Joe
Joe

Reputation: 6827

You can use UNPIVOT:

SELECT DISTINCT 
  filename,
  vals
FROM 
   (SELECT 
        filename,
        a1,
        a2,
        a3
   FROM 
      foo) p
UNPIVOT
   (vals FOR counts IN 
      (a1,a2,a3)
) AS bar

SQLFiddle here

Upvotes: 4

Related Questions