SQL unpivot row to N rows (based on column value)

Question

I have a table looking like the example below, and trying to UNPIVOT the table to hold only ONE link per row, and adding an info column, based on the UNPIVOT.

I managed to get partly to my desired result, but need a bit help for the last part. I have this Query so far:

SELECT Theme,  Area, URL
FROM
(
  SELECT Theme, Area, URL_1, URL_2, URL_3 
  FROM table

) AS a
UNPIVOT 
(
  URL FOR URLs IN (URL_1, URL_2, Url_3)
) AS b WHERE URL <> '';

How to add this extra Column, with info based on the URL?

Thank you in advance.

<h1>Have</h1>
<table border="1" cellpadding="1" cellspacing="2">
  <tr>
    <th>Theme</th>
    <th>Area</th>

    <th>URL_1</th>
    <th>URL_2</th>
    <th>URL_3</th>
  </tr>
  <tr>
    <td>A</td>
    <td>B</td>

    <td>Some URL</td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>A</td>
    <td>C</td>

    <td>Some URL</td>
    <td>Some URL</td>
    <td></td>
  </tr>

  <tr>
    <td>D</td>
    <td>B</td>

    <td>Some URL</td>
    <td>Some URL</td>
    <td></td>
  </tr>

  <tr>
    <td>B</td>
    <td>D</td>

    <td>Some URL</td>
    <td>Some URL</td>
    <td>Some URL</td>
  </tr>
</table>

<h1>Would like</h1>
<table border="1" cellpadding="1" cellspacing="2">
  <tr>
    <th>Theme</th>
    <th>Area</th>

    <th>URL</th>
    <th>Type</th>
  </tr>
  <tr>
    <td>A</td>
    <td>B</td>

    <td>Some URL</td>
    <td>1</td>

  </tr>
  <tr>
    <td>A</td>
    <td>C</td>

    <td>Some URL</td>
    <td>1</td>

  </tr>
  <tr>
    <td>A</td>
    <td>C</td>


    <td>Some URL</td>
    <td>2</td>
  </tr>

  <tr>
    <td>D</td>
    <td>B</td>

    <td>Some URL</td>
    <td>1</td>

  </tr>
  <tr>
    <td>D</td>
    <td>B</td>


    <td>Some URL</td>
    <td>2</td>
  </tr>




  <tr>
    <td>B</td>
    <td>D</td>

    <td>Some URL</td>
    <td>1</td>

  </tr>
  <tr>
    <td>B</td>
    <td>D</td>


    <td>Some URL</td>
    <td>2</td>
  </tr>
  <tr>
    <td>B</td>
    <td>D</td>


    <td>Some URL</td>
    <td>3</td>
  </tr>


</table>

<h1>What I got so far</h1>
<table border="1" cellpadding="1" cellspacing="2">
  <tr>
    <th>Theme</th>
    <th>Area</th>

    <th>URL</th>
  </tr>
  <tr>
    <td>A</td>
    <td>B</td>

    <td>Some URL</td>

  </tr>


  <tr>
    <td>A</td>
    <td>C</td>

    <td>Some URL</td>

  </tr>
  <tr>
    <td>A</td>
    <td>C</td>


    <td>Some URL</td>

  </tr>


  <tr>
    <td>D</td>
    <td>B</td>

    <td>Some URL</td>


  </tr>
  <tr>
    <td>D</td>
    <td>B</td>


    <td>Some URL</td>

  </tr>




  <tr>
    <td>B</td>
    <td>D</td>

    <td>Some URL</td>


  </tr>
  <tr>
    <td>B</td>
    <td>D</td>


    <td>Some URL</td>

  </tr>
  <tr>
    <td>B</td>
    <td>D</td>


    <td>Some URL</td>

  </tr>


</table>

look at http://stackoverflow.com/questions/19055902/unpivot-with-column-name — pkuderov, Mar 10 '16 at 13:14

score 1 · Accepted Answer · answered Mar 10 '16 at 13:16

1

short answer:

SELECT Theme,  Area, UrlKind, URL
FROM
(
  SELECT Theme, Area, URL_1, URL_2, URL_3 
  FROM table

) AS a
UNPIVOT 
(
  URL FOR UrlKind IN (URL_1, URL_2, Url_3)
) AS b WHERE URL <> '';

So name of that column is defined by FOR ... part of unpivot.

answered Mar 10 '16 at 13:16

pkuderov

3,501
2
28
46

Thank you for the quick answer. – user1865820 Mar 10 '16 at 13:22

SQL unpivot row to N rows (based on column value)

1 Answers1