CODEWITHSUNDEEP
sqlsql-server
DK ALT
DK ALT

Reputation: 2193

Creating a SQL view with columns from other table records

I have a table with data like:

A1  FID  A2
0   0   39
1   0   23
0   1   16
1   1   64
2   1   12
0   2   76
0   3   11
0   4   87

And I want to create a view that will list this:

FID Col0 Col1 Col2
0    39   23  null
1    16   64   12
2    76  null null
3    11  null null
4    87  null null

How can this be possible in T-SQL?

Upvotes: 0

Views: 172

Answers (1)

GarethD
GarethD

Reputation: 69749

You could use something like this:

SELECT  FID,
        Col0 = MAX(CASE WHEN A1 = 0 THEN A2 END),
        Col1 = MAX(CASE WHEN A1 = 1 THEN A2 END),
        Col2 = MAX(CASE WHEN A1 = 2 THEN A2 END)
FROM    T
GROUP BY FID;

Or you could use PIVOT

SELECT  pvt.FID, 
        [Col0] = pvt.[0],
        [Col1] = pvt.[1],
        [Col2] = pvt.[2]
FROM    T
        PIVOT
        (   MAX(A2)
            FOR A1 IN ([0], [1], [2])
        ) pvt

Examples of Both on SQL Fiddle

If you have an unknown number of values for A1, and therefore an unknown number of columns you cannot do this in a view. You would need to use Dynamic SQL, although this is usually better handled in the application layer, not in SQL-Server itself

Upvotes: 1

Related Questions