CODEWITHSUNDEEP
sqlsql-server
Kaja
Kaja

Reputation: 3057

Moving Column names to rows

I have such a table with date as column nameenter image description here

but I would like to have these dates in one column in more than one row something like this:

Date       Index
20170806   9206
20170813   8041
20170820   8861
20170827   8356

How can I do it in SQL Server

Upvotes: 2

Views: 231

Answers (4)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use UNPIVOT for this.

SELECT * FROM MyTable 
UNPIVOT([Date] For [Index] IN( [20170806], [20170813], [20170820], [20170827])) UNPVT

In addition, if you want to make it dynamically, you can use this query too.

DECLARE @ColNames NVARCHAR(MAX) 
    = STUFF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        (SELECT T.* FROM (VALUES(1)) AS DUMY(ID) LEFT JOIN MyTable T ON 1=2 FOR XML AUTO, ELEMENTS XSINIL )
        ,'<T xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">','')
        ,'</T>','')
        ,'_x0032_','2')
        ,' xsi:nil="true"/>','] ')
        ,'<',',[') ,1,1,'')


DECLARE @SqlQ NVARCHAR(MAX) 
    = 'SELECT * FROM MyTable UNPIVOT([Date] For [Index] IN( ' + @ColNames  + ')) UNPVT'

EXEC sp_executesql @SqlQ

Upvotes: 1

Khorshed Alam
Khorshed Alam

Reputation: 334

If you would like to go for more dynamic solution rather than hard coding all columns, the below scripts should work:

IF OBJECT_ID('TestTable') IS NOT NULL 
DROP TABLE TestTable;

CREATE TABLE TestTable
(
    [20170806] INT NOT NULL,
    [20170813] INT NOT NULL,
    [20170820] INT NOT NULL,
    [20170827] INT NOT NULL
)

INSERT INTO TestTable VALUES (9206, 8041, 8861, 8356)


DECLARE @cols NVARCHAR(MAX),
        @sql  NVARCHAR(MAX)

SELECT  @cols = COALESCE(@cols + ',', '') + QUOTENAME(c.COLUMN_NAME)
FROM   INFORMATION_SCHEMA.[COLUMNS] AS c
WHERE  c.TABLE_NAME = 'TestTable'

SET @sql = '
SELECT [Date],
       [Index]
FROM   TestTable 
       UNPIVOT([Index] FOR [Date] IN ('+ @cols +')) AS up'

exec sp_executesql @sql;

Upvotes: 1

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

Designed the table (variable) structure as below. Hopefully, it matches your table structure.

DECLARE @data TABLE
(
    [20170806] INT,
    [20170813] INT,
    [20170820] INT,
    [20170827] INT
)

INSERT INTO @data VALUES (9206, 8041, 8861, 8356)

SELECT * FROM @data

You can use UNPIVOT statement for this. If you have dynamic columns, check this.

SELECT [Date],[Index]
FROM
@data  
UNPIVOT 
(
  [Index] FOR [Date] IN ([20170806], [20170813], [20170820], [20170827])
) AS unpivotTable;

and the output is

enter image description here

Upvotes: 0

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

You could use pivot such as:

However, i dont know your exact table names 

select field_names
from table_name
pivot
( index
for index in ( Date, Index)
) pivot

but a useful article to follow is

"https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx"

Upvotes: 0

Related Questions