CODEWITHSUNDEEP
sql-server-2008pivot
MasterOfStupidQuestions
MasterOfStupidQuestions

Reputation: 925

Have Query Results Display In Different Columns

I am trying to avoid the creation of a table to have these results display (if possible). What I want to do is to essentially have the look of a table with my query results and have a column for fullitemlist, a column for 'has been ordered' and a column for 'has not been ordered'. Essentially I am trying to have a result set like such returned from my query:

> Zip = 55555
> fullitemlist ----- Has Been Ordered ---- Has Not Been Ordered
> Knife Set              1                      0
> Butcher Block          0                      1
> Dishwasher             0                      1
> Hair Dryer             1                      0

so on and so forth for all items in my tbl_itemlist. This is the 3 CTE queries I was trying to work with, but it returns everything in one giant list, not what I was after :)

Declare @zip varchar(5)
Set @zip = '55555'
;With CTE As
(
  Select fullitemlist from tbl_ItemList
  Where zip = @zip
)
,CTE2 As
(
  Select case when hasbeenordered = 1 then 1 else 0 end as 'Has Been Ordered' from tbl_purchasehistory
  WHERE itemid IN (Select itemid from tbl_ItemList where hasbeenordered = 1   and zip = @zip)
)
,CTE3 As
(
  Select case when hasbeenordered = 0 then 1 else 0 end as as 'Has NEVER Been Ordered' from tbl_purchasehistory
  WHERE itemid IN (Select itemid from tbl_ItemList where hasbeenordered = 0 and zip = @zip)
)

SELECT * from CTE
UNION ALL
SELECT * FROM CTE2
UNION ALL
SELECT * FROM CTE3

Upvotes: 0

Views: 37

Answers (1)

Arun Gairola
Arun Gairola

Reputation: 884

-- you may write your query like this according to your question instead of CTE ,union all,SubQuery this is the simplest way .

SELECT fullitemlist
    ,CASE 
        WHEN tp.hasbeenordered = 1
            AND tI.hasbeenordered = 1
            THEN 1
        ELSE 0
        END AS 'Has Been Ordered'
    ,CASE 
        WHEN tp.hasbeenordered = 0
            AND ti.tp.hasbeenordered = 0
            THEN 1
        ELSE 0
        END AS 'Has NEVER Been Ordered'
FROM tbl_ItemList ti
INNER JOIN tbl_purchasehistory tp ON ti.itemid = tp.ItemId
WHERE zip = @zip

Upvotes: 2

Related Questions