2

I've been trying to figure this out for a while. I have a table where there are multiple rows containing name value pairs, type, and date (not included for simplicity). They are all tied by an id. I would like to output all data related to the same id in one row filtered by type equals document.

  INSERT INTO table1 
    (id, name, val, type)
  VALUES 
    (111, 'direction', 'inbound', 'document'),
    (111, 'filename', 'file.txt', 'document'),
    (111, 'send', 'FOO.SND', 'document'),
    (111, 'rec', 'FOO.RCV', 'document'),
    (111, 'unrelated', 'unrelated', 'business'),
    (222, 'direction', 'inbound2', 'document'),
    (222, 'filename', 'file2.txt', 'document'),
    (222, 'send', 'FOO.SND2', 'document'),
    (222, 'rec', 'FOO.RCV2', 'document'),
    (222, 'unrelated', 'unrelated2', 'business'),
    (333, 'direction', 'inbound3', 'document'),
    (333, 'filename', 'file3.txt', 'document'),
    (333, 'send', 'FOO.SND3', 'document'),
    (333, 'rec', 'FOO.RCV3', 'document'),
    (333, 'unrelated', 'unrelated3', 'business');

What I am looking to output is:

    id   direction send      rec       filename
    111  inbound   FOO.SND   FOO.RCV   file.txt
    222  inbound2  FOO.SND2  FOO.RCV2  file2.txt
    333  inbound3  FOO.SND3  FOO.RCV3  file3.txt
Laurie
  • 21
  • 2

1 Answers1

0

Try a pivot query:

SELECT id,
       MAX(CASE WHEN name = 'direction' THEN val END) AS direction,
       MAX(CASE WHEN name = 'send'      THEN val END) AS send,
       MAX(CASE WHEN name = 'rec'       THEN val END) AS rec,
       MAX(CASE WHEN name = 'filename'  THEN val END) AS filename
FROM table1
GROUP BY id
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you! This helped push me in the right direction. – Laurie Oct 25 '16 at 14:40
  • Sorry, because I have less than 15 reputation it is recorder but not publicly displayed. That is the message I received when upvoted . – Laurie Oct 25 '16 at 19:34