Convert Columns Of Data To Rows Of Data In Sql Server

I'm trying to learn how to import data from a csv file to my database and then reorganise it into my tables. I have imported a very simple set of data into a table called ' CSVTest

Solution 1:

" Convert Columns of Data to Rows of data in SQL Server ?". I started searching Google on your questions, I got these much answers.

Visit these links: "Converting Columns into rows with their respective data in sql server" or "Convert row data to column in SQL Server" or "SQL query to convert columns into rows" or "How to convert Columns into Rows in Oracle?" or

http://forums.asp.net/t/1851916.aspx?Converting+Column+values+to+Rows+in+SQL+Query

Solution 2:

I don't remember where I swiped this from, probably from around here, but here you go, I've been using this for a while.. This assumes the column 'names' are Indicator1-x and the table is yourtable. Search and replace accordingly. If you don't know your header names ahead of time, do a select distinct on them, and then do C.column_name in

DECLARE@colsUnpivotAS NVARCHAR(MAX),
   @queryAS NVARCHAR(MAX)

select@colsUnpivot= stuff((select','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name ='CSVTest_Match'and
                 C.column_name in ('Home Team','Away Team','Kick Off Time','Kick Off Date','Home Goals','Away Goals')
           for xml path('')), 1, 1, '')

set@query='select id, entityId,
        indicatorname,
        indicatorvalue
     from CSVTest_Match
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+@colsunpivot+')
     ) u'exec sp_executesql @query;

Solution 3:

SELECT * FROM 
(SELECT Header, Data FROM CSVTest_Match) AS T
PIVOT (Min(Data) FOR Header IN ([Home Team], [Away Team], [Kick Off Time], 
                [Kick Off Date], [Home Goals], [Away Goals])) AS T2