How to create multiple rows from a single row

Question

I have a table called result that has 11 columns and look like that

Name Seat FA1 FA2 Fa3 FB1 FB2 FB3 FC1 FC2 FC3

what I am trying to do is break each row into 3 rows so the rows can be like this

Name Seat FA1 FB1 FC1

Name Seat FA2 FB2 FC2

Name Seat FA3 FB3 FC3

Posting an answer doesn't seem to work but typically, you would use a `UNION ALL`. Alternatively, you could look into `PIVOT/UNPIVOT` but that might be overkill. — Lieven Keersmaekers, Oct 09 '13 at 10:50

score 1 · Answer 1 · answered Oct 09 '13 at 10:51

If the types match, you could use...

SELECT Name, Seat, FA1, FB1, FC1 from result

UNION ALL

SELECT Name, Seat, FA2, FB2, FC2 from result

UNION ALL

SELECT Name, Seat, FA3, FB3, FC3 from result

This will depend on whether you want to filter out results, you may want to only show lines where you have values in all...

SELECT Name, Seat, FA1, FB1, FC1 
from result
WHERE (FA1 IS NOT NULL AND FB1 IS NOT NULL AND FC1 IS NOT NULL)

Paul Maxwell · Accepted Answer · 2013-10-09T11:08:12.870

1

I've found this technique - using a combination of cross apply and values to be extremely helpful - I got this from here where the author (Kenneth Fisher) argues it on performance grounds over use of unpivot. What I like most is the ease of layout control it provides and think it's easier than unpivot. It will work in SQL 2008.

SELECT
    Name
  , Seat
  , CA1.Aaisle
  , CA1.Baisle
  , CA1.Caisle
FROM UnPivotMe
CROSS APPLY (
    VALUES 
                (FA1, FB1, FC1)
              , (FA2, FB2, FC2)
              , (FA3, FB3, Fc3)
            ) AS CA1 (Aaisle, Baisle, Caisle)
;

EDIT - sorry altered aliases to something more logical

edited Oct 09 '13 at 11:08

answered Oct 09 '13 at 11:00

Paul Maxwell

33,002
3
32
51

+1 but you should rename to `aas, bees, cees` – Lieven Keersmaekers Oct 09 '13 at 11:07
:) as long as it's understood - the naming chosen is up to the user – Paul Maxwell Oct 09 '13 at 11:24

How to create multiple rows from a single row

2 Answers2