Convert Columns of Data to Rows of data in SQL Server

Question

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_Match' that looks like this:

HEADER          DATA             --(Column Names)
--------------- --------------
Home Team       Barnsley
Away Team       Wigan
Kick Off Time   14:02
Kick Off Date   03/08/2013
Home Goals      0
Away Goals      4

The values in both columns are VARCHAR's at this point. I would like to transform this data to look like this:

HOMETEAM   AWAYTEAM   KICKOFFTIME   KICKOFFDATE   HOMEGOALS   AWAYGOALS  -- (Column Names)
---------- ---------- ------------- ------------- ----------- ----------
Barnsley   Wigan      14:02         03/08/2013    0           4

At this point it would be useful if the data was converted to VARCHAR, DATETIME, TINYINT values as appropriate. I've been getting rather confused trying to work out how to use PIVOT to do this so I would really appreciate some help.

EDIT: I finally figured it out. The code required was:

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

a good sample how PIVOT works - http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Moslem Ben Dhaou, Mar 17 '14 at 12:59

XeroxDucati · Answer 1 · 2014-03-17T15:45:43.960

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 @colsUnpivot AS NVARCHAR(MAX),
   @query  AS 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;

I'm really new to this. Please could you modify the code using my parameters as described in the OP as I am struggling to make this work. Thanks. — Steve W, Mar 17 '14 at 14:33
I edited the procedure to match what you're after.. Not having SQL handy I did this by hand, YMMV — XeroxDucati, Mar 17 '14 at 15:46

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

" 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

edited May 23 '17 at 12:21

Community

1
1

answered Mar 17 '14 at 12:58

rajmathan

651
6
27

Please consider searching before asking a question to save time and get a quicker answer for yourself. – rajmathan Mar 17 '14 at 12:59
Honestly I have spent all morning looking at the links like the ones you posted. I still have not figured out how to answer my question, hence the post. All of the examples I have found seem to require an aggregation function, but my example does not require one - its a simple transform. So I have not yet been able to solve my problem. – Steve W Mar 17 '14 at 13:28

score 0 · Accepted Answer · answered Mar 19 '14 at 15:09

0

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

answered Mar 19 '14 at 15:09

Steve W

1,108
3
13
35

Convert Columns of Data to Rows of data in SQL Server

3 Answers3

Linked