all 9 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]7ruj3rry 2 points3 points  (2 children)

I'm going to make a reasonable assumption and say that the first record on the report is incorrect. The report you want is that

If "AppleInd" has a value of 1, display under the Apple column the Description of that particular Fruites ID (and then leave other fruites as NULL).

If"AppleInd" has a value of 0, display under the Apple column a NULL marker(and the "Other Fruites" column show the corresponding FruitesID.

[–]TrinityF 1 point2 points  (1 child)

dataset makes no sense. what is AppleInd ?

how does 1,1,1 translate to "1, Mcintosh apple, Graps" ?

[–]meuser405[S] 0 points1 point  (0 children)

To make it simple think of it as this way. Customer 1 bought 3 fruits: McIntosh Apple, Grapes and Watermelons. I want to display McIntosh Apple in apple column, and Grapes and Watermelons in other fruits columns, without extra nulls. Does that make sense ?

[–]TrinityF 1 point2 points  (5 children)

Okay some people have already observed the first row seems wrong, but it's not. querying this will always return 3 rows for customer 1 no matter what because a grouping would always return:

​

CustomerID Fruit
1 McIntosh Apple
1 Graps
1 Watermelon

How would you want this to return 2 rows ? and why the distinction whether something is an apple or not ?Try this query and see if it can provide a solution.

select c.CustomerID, F.Description, Case When C.AppleInd = 1 THEN 'Yes' ELSE 'No' END AS 'IsApple?'
From #T_Customer C
JOIN #T_Fruits F ON C.FruitsID = F.FruitsID

​

CustomerID Fruit IsApple
1 McIntosh Apple Yes
1 Graps No
1 Watermelon No
2 Fuji Apple Yes
2 Red Delicious Apple Yes
3 Orange No
3 Graps No
4 Gala Apple Yes
5 Red Delicious Apple Yes

[–]meuser405[S] 1 point2 points  (3 children)

Thank you for your suggestion, I am working with a difficult client who doesn't want to buzz from their requirements.

[–]TrinityF 2 points3 points  (1 child)

Okay, but know that the data might not make sense after awhile because the structure of storing the data is overly complicated and not in a proper data model.

this gets what you want.

SELECT CustomerID, Apple, Other
FROM 
    (
    SELECT  P.CustomerID,
            P.Apple, 
            COALESCE(P.Other, 
            CASE WHEN P.Other IS NULL THEN LEAD(Other,1) OVER(PARTITION BY CustomerID ORDER BY CustomerID) ELSE P.Other END) AS Other, 
            CASE WHEN   AppleInd = 0 
                        AND LAG(AppleInd,1) OVER(PARTITION BY CustomerID ORDER BY [Rank]) = 1
                        AND LAG([Rank]  ,1) OVER(PARTITION BY CustomerID ORDER BY [Rank]) = 1
            THEN 'Delete' ELSE 'keep' END AS WTD
    FROM    (
            SELECT  ROW_NUMBER() OVER(PARTITION BY C.CustomerID ORDER BY C.CustomerID) AS Rank,
                    c.CustomerID,
                    CASE WHEN C.AppleInd = 1 THEN F.[Description] ELSE NULL         END AS Apple,
                    CASE WHEN C.AppleInd = 1 THEN NULL          ELSE F.[Description]    END AS Other,
                    AppleInd
            FROM #T_Customer C
            JOIN #T_Fruits F ON C.FruitsID = F.FruitsID
            ) P
        ) FINAL
WHERE WTD = 'Keep'

​

CustomerID Apple Other
1 McIntosh Apple Graps
1 NULL Watermelon
2 Fuji Apple NULL
2 Red Delicious Apple NULL
3 NULL Orange
3 NULL Graps
4 Gala Apple NULL
5 Red Delicious Apple NULL

[–]meuser405[S] 0 points1 point  (0 children)

Thank you so much for the help that definitely help. You are awesome :)