July 5, 2018 at 10:50 am
I have a CTE that results in a pivot, now I want to bring in the results to another query. I can't seem to get the pivot table alias correct so I can tie it in. Here is what I have and am trying to do.
; WITH S AS (
SELECT DISTINCT m.item_id AS 'Old'
, m2.item_id AS 'Sub'
, ROW_NUMBER () OVER (PARTITION BY m.item_ID ORDER BY m2.item_id ) AS 'RN'
FROM inv_mast m
INNER JOIN inv_sub s ON s.inv_mast_uid = m.inv_mast_uid
INNER JOIN inv_mast m2 ON m2.inv_mast_uid = s.sub_inv_mast_uidWHERE s.interchangeable = 'Y' AND s.delete_flag = 'N'
)
SELECT * FROM S
pivot
(
MAX(Sub) FOR RN IN ([1], [2], [3])
) piv;
/* Have the above already working, but want to bring in results with another table, so I want something like below. Just can't get JOIN right. */
SELECT l.order_no
,
.1
,
.2
,
.3FROM invoice_line l
INNER JOIN
ON
.Old = l.item_id
July 5, 2018 at 12:01 pm
Can you provide some data and DDL for the tables?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2018 at 12:13 pm
The easiest way is to create a new CTE.
WITH S AS (
SELECT DISTINCT
m.item_id AS 'Old'
, m2.item_id AS 'Sub'
, ROW_NUMBER () OVER (PARTITION BY m.item_ID ORDER BY m2.item_id ) AS 'RN'
FROM inv_mast m
INNER JOIN inv_sub s ON s.inv_mast_uid = m.inv_mast_uid
INNER JOIN inv_mast m2 ON m2.inv_mast_uid = s.sub_inv_mast_uid
WHERE s.interchangeable = 'Y'
AND s.delete_flag = 'N'
),
Piv AS(
SELECT Old,
MAX(CASE WHEN RN = 1 THEN Sub END) AS [1],
MAX(CASE WHEN RN = 2 THEN Sub END) AS [2],
MAX(CASE WHEN RN = 3 THEN Sub END) AS [3]
FROM S
GROUP BY old
)
SELECT l.order_no
, Piv.[1]
, Piv.[2]
, Piv.[3]
FROM invoice_line l
INNER JOIN Piv ON Piv.Old = l.item_id;
--US46849
July 5, 2018 at 12:45 pm
Luis, This is perfect, I was trying to alias the SELECT * FROM S pivot (... and couldn't get it. Now that I see it in type, it makes sense.
Mike, I could send some data if you still want to mess with this, but Luis's solution will do what I need. I appreciate both of you taking the time to help me out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply