Three solutions to unpivot a table
traditional way: create all categories (by recursive CTE), cross join and case when\n\nWITH recursive CTE AS (\n\t\t\t\t\t\tSELECT 1 as num\n\t\t\t\t\t\t UNION 
1. traditional way: create all categories (by recursive CTE), cross join and case when\n```\nWITH recursive CTE AS (\n\t\t\t\t\t\tSELECT 1 as num\n\t\t\t\t\t\t UNION ALL\n\t\t\t\t\t    SELECT num+1 as num \n\t\t\t\t\t      FROM CTE\n\t\t\t\t        WHERE num+1<=3)\n, CJ AS (\n\t\t\tSELECT DISTINCT product_id,\n\t\t\t\t   CASE WHEN num = 1 THEN \'store1\' WHEN num = 2 THEN \'store2\' WHEN num = 3 THEN \'store3\' END AS store,\n\t\t\t       CASE WHEN num = 1 THEN store1 WHEN num =2 THEN store2 WHEN num=3 THEN store3 END AS price\n\t\t\t\t\t   FROM Products\n\t\t\t\t CROSS JOIN CTE)\nSELECT * FROM CJ WHERE price IS NOT NULL\n```\n2. union all (if there are not too many categories/categories are provided)\n```\nSELECT DISTINCT product_id, \'store1\' as store, store1 as price\n  FROM Products\n WHERE store1 IS NOT NULL\n UNION\nSELECT DISTINCT product_id, \'store2\' as store, store2 as price\n  FROM Products\n WHERE store2 IS NOT NULL\n UNION\nSELECT DISTINCT product_id, \'store3\' as store, store3 as price\n  FROM Products\n WHERE store3 IS NOT NULL\n ```\n3. function UNPIVOT (MSSQL)\n```\nSELECT DISTINCT product_id, store1 as store\n  FROM Products\n WHERE store1 IS NOT NULL\n ```
[]