Sql-server – Display all tables and their columns

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.

This query uses both UNPIVOT and then PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10))
    FROM @data
) d
UNPIVOT (
    [value] FOR [Data] IN (ColA, ColB, ColC, ColD)
) as unpiv
PIVOT (
    MAX([value]) 
    FOR [Type] IN ([x], [y])
) as piv
;

• It first unpivots your data in order to get a regular table
• It then pivots this table back to the new required format

I have to cast everything as varchar. Based on your data model, you may have to cast them to another type. An aggregate function is required by PIVOT. Since this is a 1 to 1 match, it works with either of MAX or MIN.

With SQL Server >= 2008 you can also replace UNPIVOT by Table Value Constructor and CROSS APPLY along with PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT v.[Type], [value], [Data]
    FROM @data d
    CROSS APPLY (values
        (d.[type], CAST(d.ColA as varchar(10)), 'ColA')
        , (d.[type], CAST(d.ColB as varchar(10)) , 'ColB')
        , (d.[type], CAST(d.ColC as varchar(10)), 'ColC')
        , (d.[type], CAST(d.ColD as varchar(10)), 'ColD')
    ) as v([type], [value], [Data])
) unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

Without PIVOT/UNPIVOT you can use this query with any (old) version of SQL Server:

SELECT [Data]
    , [x] = MAX(CASE WHEN [type] = 'x' THEN [value] END)
    , [y] = MAX(CASE WHEN [type] = 'y' THEN [value] END)
FROM (
    SELECT [type], [value] = CAST(ColA as varchar(10)), [Data] = 'ColA' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColB as varchar(10)), [Data] = 'ColB' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColC as varchar(10)), [Data] = 'ColC' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColD as varchar(10)), [Data] = 'ColD' FROM @data
) as v
GROUP BY [Data]

Output:

Data    x       y
ColA    123456  654321
ColB    $500    $200
ColC    6       36
ColD    30      90

Data:

Declare @data table([Type] char(1), ColA bigint, ColB varchar(10), ColC int, ColD int);
INSERT INTO @data([Type], ColA, ColB, ColC, ColD) VALUES
    ('x', 123456, '$500', 6, 30)
    , ('Y', 654321, '$200', 36, 90);