Microsoft TSQL change text row to column

Question

I want to change the 20 rows with one column to 1 row with 20 columns to insert it later in a second database

Name
----------
- Frank
- Dora
- ...
- Michael

to

Name1 | Name2 | ... | Name20
Frank | Dora  | ... | Michael

I tried

SELECT * 
FROM  (SELECT TOP 20 firstname AS NAME 
       FROM   database) AS d 
      PIVOT (Min(NAME) 
            FOR NAME IN (name1, 
                         name2, 
                         name3, 
                         name4, 
                         name5, 
                         name6, 
                         name7, 
                         name8, 
                         name9, 
                         name10, 
                         name11, 
                         name12, 
                         name13, 
                         name14, 
                         name15, 
                         name16, 
                         name18, 
                         name19, 
                         name20) ) AS f

But all names are NULL. DEMO

Possible duplicate of [SQL Server : Transpose rows to columns](http://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns) — Thorsten Dittmar, Sep 22 '16 at 08:18

score 1 · Accepted Answer · answered Sep 22 '16 at 08:24

1

You were close... But your inner select must carry the new column name. Try it like this:

DECLARE @tbl TABLE(Name VARCHAR(100));
INSERT INTO @tbl VALUES('Frank'),('Dora'),('Michael');

SELECT p.*
FROM
(
    SELECT 'Name' + CAST(ROW_NUMBER() OVER(ORDER BY Name) AS VARCHAR(150)) AS ColumnName
          ,Name
    From @tbl
) AS tbl
PIVOT
(
    MIN(Name) FOR ColumnName IN(Name1,Name2,Name3)
) AS p

answered Sep 22 '16 at 08:24

Shnugo

66,100
9
53
114

Thank you for your fast answer, but I do´n´t get it. The names are a result of a subquery and after transforming to 1 row with 20 columns they should insert a second database... – mydefs Sep 22 '16 at 08:31
@mydefs you own code is not more than a select... The inner select will create a list of name and a running "Name1", "Name2", "Name3"... as many as there are in the list (you can cut it with `TOP 20`). After this the `PIVOT` will sort the names into new *columns* according to their second value (which is the new column's name). Just Replace the `@tbl` with your real table's name... If you add `INSERT INTO SomeTable` as first line, you can add this to a table with a fitting definition. – Shnugo Sep 22 '16 at 08:35
@XpiritO, [Read this link, especially the *Note*](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) – Shnugo Sep 29 '16 at 13:43

Microsoft TSQL change text row to column

1 Answers1