Using Pivot in SQL

Question

For example I have a table

IPPARSED
127
0
0
1

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?

Answer 1

Hi don't you have another column in your table to aggregate the IP addresses? Yous should not have only one integer per record without any other group column, do you? What DB do you use?

Answer 2

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.

Answer 3

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?

Answer 4

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.