Concatenate lines into one

Asked

Viewed 30 times

0

Good afternoon, I need to unify some data, when I bring this so:

id 2017 2017-LOJA   2018    2018-LOJA
1   n       n        s          s
1   s       s       n           n  

Would like so:

id 2017 2017-LOJA   2018    2018-LOJA
1   s       s        s          s

consultation I made:

SELECT id, 
CASE WHEN YEAR(DATA) = 2017 THEN 'S' END AS '2017',
CASE WHEN YEAR(DATA) = 2017 AND ORIGEM = 'LOJA' THEN 'S' END AS '2017 - LOJA',
CASE WHEN YEAR(DATA) = 2018 THEN 'S' END AS '2018',
CASE WHEN YEAR(DATA) = 2018 AND ORIGEM = 'LOJA' THEN 'S' END AS '2018 - LOJA'
FROM table
WHERE ORIGEM = 'LOJA'

1 answer

1


Try

SELECT id, 
       max (CASE WHEN YEAR(DATA) = 2017 THEN 'S' END) AS [2017],
       max (CASE WHEN YEAR(DATA) = 2017 AND ORIGEM = 'LOJA' THEN 'S' END) AS [2017 - LOJA],
       max (CASE WHEN YEAR(DATA) = 2018 THEN 'S' END) AS [2018],
       max (CASE WHEN YEAR(DATA) = 2018 AND ORIGEM = 'LOJA' THEN 'S' END) AS [2018 - LOJA]
  FROM table
  WHERE ORIGEM = 'LOJA'  
  group by id;

Browser other questions tagged

You are not signed in. Login or sign up in order to post.