Using Pivot in SQL


For example I have a table


now I need to pivot the table so it can come like this

1       2   3   4
127   0   0   1

Can you help me how?


You could try using case statements to pivot your table assuming the rows always go in that order:

SELECT (case when (<condition to match first octet>) then IPPARSED else END) as 1,
       (case when (<condition to match second octet>) then IPPARSED else END) as 2,
       (case when (<condition to match third octet>) then IPPARSED else END) as 3,
       (case when (<condition to match fourth octet>) then IPPARSED else END) as 4
FROM <insert_table_name_or_table_definition_here>

Cf. Anthony Mollinaro's SQL Cookbook pg 365.

EDIT: if your having trouble coming up with a <condition to match (...) octet>, you'll need to have the select operate on the results of a query that adds linenumbers to your table somehow. In Oracle, you'd use the rownum psuedocolumn, but that doesn't exist in SQL Server. It does appear that in SQL Server 2005 you can use the ROW_NUMBER() built-in function as column, or possibly use the IDENTITY(...) function as a column in that query.

By : J. Polfer

I would agree with sheepsimulator's answer, but as you commented on my answer David, if you have no grouping column and several entries in your table it will be difficult to find a matching condition. If you have only one IP entry (so 4 lines) then you can:

  • use the lineID (rownum in Oracle) to group
  • parse the 4 lines ;-)

EDIT: the first solution (using lineID) can be used to parse several IP entries, but it needs sequential inserts in the DB.

I think that you are doing something strangely when a parsed IP comes out as four rows. It should be quite easy to parse directly to a row. Could you perhaps provide your parsing method and/or input format?

By : Svante

This video can help you solving your question :)
By: admin