SQL Columns to List

Question

I have the following table contents:-

And I need to produce the following output:-

P1 C1
P1 C3
P2 C1
P2 C4
P3 C2
P3 C3
P3 C4

How can I create that list from my table?

Thanks

P

I have tried to use Unpivot but I am quite unfamiliar with it. I think its the way I need to go but I don't know how to use it properly — Powell21, May 18 '16 at 08:15
You can check http://stackoverflow.com/questions/19055902/unpivot-with-column-name — Ako, May 18 '16 at 08:23
I can get the Unpivot syntax, I'm just having trouble applying it to my issue — Powell21, May 18 '16 at 08:29

score 3 · Accepted Answer · answered May 18 '16 at 09:15

You have to use UnPivot to get the desired result

    DECLARE @MyTable TABLE
(Attribute VARCHAR(10) , C1 VARCHAR(10), C2 VARCHAR(10), C3 VARCHAR(10), C4 VARCHAR(10))

INSERT INTO @MyTable VALUES
('P1','X', NULL,'X',NULL), ('P2','X',NULL,NULL,'X'),('P3',NULL,'X','X','X')

SELECT * FROM @MyTable

SELECT uPivot.Attribute, uPivot.Quatr, uPivot.IsMarked
FROM @MyTable Tab1
UNPIVOT
        (
          IsMarked
          for Quatr in (C1, C2, C3, C4)
        ) uPivot;

SQL FIDDLER EXAMPLE

Thanks very much for your help – Powell21 May 18 '16 at 09:18 — Powell21, May 18 '16 at 09:18

score 1 · Answer 2 · answered May 18 '16 at 09:14

1

This should work based off the information provided.

select u.P, -- whatever the column containing p1, p2, etc is called
u.C,
u.Contents -- what is stored in each cell
from xtable
unpivot
(
  Contents
  for C in (c1, c2, c3)
) u
where u.Contents like 'x'

http://sqlfiddle.com/#!3/4d908/1/0

I pretty much slightly modified the example given in what Ako linked in the comments.

answered May 18 '16 at 09:14

Chris

915
8
28

Thanks very much for your help – Powell21 May 18 '16 at 09:18

score 0 · Answer 3 · answered May 18 '16 at 10:03

This was the solution I implemented in the end

DECLARE @SQL NVARCHAR(MAX)
DECLARE @ColName TABLE (Name VARCHAR(500))
DECLARE @ColCSV VARCHAR(MAX)

INSERT INTO  @ColName 
SELECT
    Column_Name
FROM
    information_schema.columns
WHERE
    Table_Name = 'SMC_PatientCondition'
    AND Column_Name NOT IN 
        ('ReportingCode',
        'P_PatientId',
        'P_SysId',
        'Condition')

SET @ColCSV = (SELECT SUBSTRING((SELECT ',' + Name FROM @ColName FOR XML PATH ('')),2,2000) AS CSV)

SET @SQL =
('
SELECT
    P_SysID,
    Conditions
FROM
    (
        SELECT
            P_SysId,
            RIGHT(Conditions, LEN(Conditions)-2) Conditions,
            HasCondition
        FROM
            SMC_PatientCondition
        UNPIVOT
        (
            HasCondition FOR Conditions IN ('+ @ColCSV +')
        )Con
    ) UserConditions
WHERE
    HasCondition = ''Yes''
')

EXEC sp_executesql @SQL

SQL Columns to List

3 Answers3