I created a list with a Query

how to arrange the list otherwise? Like this:

I tried the STRING_SPLIT Function in T-SQL, but i does not work with a Subquery inside like

Select value FROM String_Splitt ((Select * FROM TABLE1), ',')

CodePudding user response：

We can try to use CROSS APPLY with VALUES to make unpivot

SELECT v.*
FROM T CROSS APPLY (VALUES 
 ('a',a),
 ('b',b),
 ('c',c)
) v (name,val)

if you have more than three columns we can just put them after VALUES

SELECT v.*
FROM T CROSS APPLY (VALUES 
 ('a',a),
 ('b',b),
 ('c',c),
 -- more columns 
) v (name,val)

SQLFIDDLE

CodePudding user response：

Please try the following solution.

It is completely generic regardless of the number of columns in a table.

Converting a db table into key-value pairs.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ColA VARCHAR(10), ColB CHAR(3), ColC INT);
INSERT INTO   @tbl (ColA, ColB, ColC) VALUES
('Abc', 'Bcd', 123),
('Cde', 'Def', 234);
-- DDL and sample data population, end

SELECT * FROM @tbl;

SELECT p.ID --p.* , c
    , x.value('local-name(.)', 'VARCHAR(20)') as [col_name]
    , x.value('(./text())[1]', 'VARCHAR(100)') AS [col_value]
FROM @tbl AS p
CROSS APPLY (SELECT * FROM @tbl AS c WHERE p.ID = c.ID
      FOR XML PATH(''), TYPE, ROOT('root')) AS t1(c)
CROSS APPLY t1.c.nodes('/root/*') AS t2(x);

Output

 ---- ---------- ----------- 
| ID | col_name | col_value |
 ---- ---------- ----------- 
|  1 | ID       | 1         |
|  1 | ColA     | Abc       |
|  1 | ColB     | Bcd       |
|  1 | ColC     | 123       |
|  2 | ID       | 2         |
|  2 | ColA     | Cde       |
|  2 | ColB     | Def       |
|  2 | ColC     | 234       |
 ---- ---------- -----------