SQL Server Flatten Data

Question

I have 3 tables:

tUsers
  -uid

tColors
  -colorid
  -colorname

tColors_User_Detail
  -uid_fk
  -colorid_fk

Users select which colors they like, and only the colors they like. This creates records in tColors_User_Detail. I need to flatten this out, so that each user has one record with the color from tColors as a column name, and they have a True/False value in the row for each color depending on if they had a record in tColors_User_Detail. If the user did not have a color selected in tColors_User_Detail, it would be a False value in the specific color column. And, if they do have a record in tColors_User_Detail for a color, it would be a true value for the corresponding color column.

Any help appreciated.

Also sample input and ouput data with few rows to explain the desired output. — Utsav, Sep 15 '15 at 15:30
Google the PIVOT operator, or indeed other pivot solutions. These are designed to do what you want. — The Dumb Radish, Sep 15 '15 at 15:35

Jason W · Answer 1 · 2015-09-15T16:59:34.290

2

Here's a basic PIVOT example with a COALESCE to show 'false' if no value is available. This assumes you have to hard-code the names of the colors for the column names.

DECLARE @tUsers TABLE ([uid] INT)
DECLARE @tColors TABLE ([colorid] INT, [colorname] VARCHAR(50))
DECLARE @tColors_User_Detail TABLE ([uid_fk] INT, [colorid_fk] INT)
INSERT @tUsers VALUES (1),(2)
INSERT @tColors VALUES (1,'Blue'),(2,'Red'),(3,'Green')
INSERT @tColors_User_Detail VALUES (1,1),(1,2),(1,3),(2,1)

SELECT
    uid,
    COALESCE([Red], 'False') AS [Red],
    COALESCE([Blue], 'False') AS [Blue],
    COALESCE([Green], 'False') AS [Green]
FROM @tUsers U
    LEFT OUTER JOIN @tColors_User_Detail CUD
        ON CUD.uid_fk = U.uid
    LEFT OUTER JOIN @tColors C
        ON C.colorid = CUD.colorid_fk
    PIVOT (MAX(colorname) FOR colorname IN (
        [Red],
        [Blue],
        [Green]
    )) PVT

If you want to let the columns be dynamic from the colors, you'll have to use dynamic sql.

DECLARE @Sql VARCHAR(1000) =
    'SELECT uid'
    + (SELECT ', CASE WHEN [' + [colorname] + '] IS NOT NULL THEN ''True'' ELSE ''False'' END AS [' + [colorname] + ']' AS [text()] FROM tColors FOR XML PATH(''))
    + ' FROM tUsers  U
            LEFT OUTER JOIN tColors_User_Detail CUD
                ON CUD.uid_fk = U.uid
            LEFT OUTER JOIN tColors C
                ON C.colorid = CUD.colorid_fk
            PIVOT (MAX(colorname) FOR colorname IN ('
    + SUBSTRING((SELECT ',[' + [colorname] + ']' AS [text()] FROM tColors FOR XML PATH('')), 2, 1000)
    + ')) PVT'
EXEC (@Sql)

edited Sep 15 '15 at 16:59

answered Sep 15 '15 at 15:51

Jason W

13,026
3
31
62

Thanks. Yes, I need the color names to be pulled from the tColors table, not hard coded, as the available colors may change over time. – devnuts Sep 15 '15 at 15:54
Thanks, I'll see what I can do with this. – devnuts Sep 15 '15 at 16:04
Thanks, Jason. The records would need to be distinct, one record per user, and the values in the fields should be True / False. How would I modify? – devnuts Sep 15 '15 at 16:44
The PIVOT ensures you only have 1 record per user (PIVOT has the aggregate in the statement). I updated the `SELECT` columns to use a `CASE` to determine whether just TRUE/FALSE is displayed. Hope that helps! – Jason W Sep 15 '15 at 17:00
I expect PIVOT to make it distinct as well, but the results have multiple rows per user, equaling the number of rows they have in the tColors_User_Detail. It appears to be just flattening out each row. So if there are 3 records in tColors_User_Detail, it is flattening that out to have colors as the columns, and putting the true false value in the correct column, but still has 3 records. Appears I would need to MAX each column at those point to make distinct. – devnuts Sep 15 '15 at 17:17
Got it working by adding MAX around the case statements, and then grouping by uid after the PVT. Thanks for your help! – devnuts Sep 15 '15 at 17:39

score 1 · Answer 2 · answered Sep 15 '15 at 16:59

What flavor of SQL?

Something along the lines of: http://sqlfiddle.com/#!6/ec4e2

SELECT U.uid
  , C.colorid
  , C.colorname
  , ( CASE WHEN cud.uid_fk IS NOT NULL THEN 'True' ELSE 'False' END ) AS ColorChosen
FROM tUsers U
FULL OUTER JOIN tColors C ON 1=1
LEFT OUTER JOIN tColors_User_Detail cud ON 
  U.uid = cud.uid_fk
  AND C.colorid = cud.colorID_FK

EDIT: I missed the pivot for one row per user. Meeting time though. Be back in a bit.

SQL Server Flatten Data

2 Answers2