Help with SQL Query

SELECT
      I.ItemID
    , I.NAME
    , max(CASE WHEN d.discountID = 1 THEN 'Discount 1' END) AS d1
    , max(CASE WHEN d.discountID = 1 AND d.discount = 0 THEN 'No' ELSE 'Yes' END) 'discount1'
    , max(CASE WHEN d.discountID = 2 THEN 'Discount 2' END) AS d2
    , max(CASE WHEN d.discountID = 2 AND d.discount = 0 THEN 'No' ELSE 'Yes' END) 'discount2'
    , max(CASE WHEN d.discountID = 3 THEN 'Discount 3' END) AS d3
    , max(CASE WHEN d.discountID = 3 AND d.discount = 0 THEN 'No' ELSE 'Yes' END) 'discount3'
    , max(CASE WHEN d.discountID = 4 THEN 'Discount 4' END) AS d4
    , max(CASE WHEN d.discountID = 4 AND d.discount = 0 THEN 'No' ELSE 'Yes' END) 'discount4'
    , max(CASE WHEN d.discountID = 5 THEN 'Discount 5' END) AS d5
    , max(CASE WHEN d.discountID = 5 AND d.discount = 0 THEN 'No' ELSE 'Yes' END) 'discount5'
FROM Database1.dbo.Item I
INNER JOIN database2.dbo.discount d ON I.itemID = d.Itemid
WHERE P.Product = 'Product 1'
GROUP BY I.ItemID
    , I.NAME

Select allOpen in new window

actually, I think this would work without needing the group by:

SELECT
      I.ItemID
    , I.NAME
    , 'Discount 1' AS d1
    , CASE WHEN d.discountID = 1 AND d.discount = 1 THEN 'Yes' ELSE 'NO' END 'discount1'
    , 'Discount 2' AS d2
    , CASE WHEN d.discountID = 2 AND d.discount = 1 THEN 'Yes' ELSE 'NO' END 'discount2'
    , 'Discount 3' AS d3
    , CASE WHEN d.discountID = 3 AND d.discount = 1 THEN 'Yes' ELSE 'NO' END 'discount3'
    , 'Discount 4' AS d4
    , CASE WHEN d.discountID = 4 AND d.discount = 1 THEN 'Yes' ELSE 'NO' END 'discount4'
    , 'Discount 5' AS d5
    , CASE WHEN d.discountID = 5 AND d.discount = 1 THEN 'Yes' ELSE 'NO' END 'discount5'
FROM Database1.dbo.Item I
INNER JOIN database2.dbo.discount d ON I.itemID = d.Itemid
WHERE P.Product = 'Product 1'
ORDER BY
      I.ItemID

Select allOpen in new window

{+ edit test for 1 instead of 0, correction)

This one gives you one row per distinct item and gives dynamic columns based on what discounts you have:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((
	SELECT distinct ', [Discount ' + CONVERT(varchar(10),DiscountID) + ' Type]=''Discount ' + CONVERT(varchar(10),DiscountID) + ''', 
	[Discount ' + CONVERT(varchar(10),DiscountID) + ']=(SELECT CASE Discount WHEN 1 THEN ''Yes'' ELSE ''No'' END FROM database2.dbo.Discount d WHERE d.ItemId=I.ItemId AND d.DiscountID=' + CONVERT(varchar(10),DiscountID) + ')'
	FROM Discount
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT ItemId, ItemName,' + @cols + ' FROM Database1.dbo.Item I WHERE P.Product = ''Product 1''' 
execute(@query)

Select allOpen in new window

Sincere THANKS to all of you for your quick response. here are the details after I have tried everything.

PortletPaul
Order by didn't work since it was returning  five rows per the item
Group by worked based on total no.of rows I was expecting, but the discount data for all of the items says or return as "Yes"

Beartlaoi
Your last script is working and getting the correct results, but is there any way I can re-name the columns (as an example Discount 1 as Clarence, Discount 2 as Closeout etc)

Razmus
No issues with your script and working good with the correct results and looks simple too.

THANKS again guys.
Have a good day

Yes, Lets create a DiscountNames table in the same database as the Discount table where you can set the names of the discounts without editing the SQL every time you change them.

CREATE TABLE database2.dbo.DiscountNames]( [DiscountID] [int] NOT NULL, [DiscountName] [varchar](50) NOT NULL), CONSTRAINT [PK_DiscountNames] PRIMARY KEY CLUSTERED ([DiscountID] ASC)

Fill it in (for example):
DiscountID      DiscountName
1      Clearence
2      Closeout
3      Employee
4      Military
5      Student
6      Senior
7      Coupon

DECLARE @cols1 AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols1 = STUFF((
	SELECT distinct ', [' + CONVERT(varchar(10),d.DiscountID) + '] as [' + ISNULL(dn.DiscountName, 'Discount ' + CONVERT(varchar(10),d.DiscountID)) + ']'
	FROM database2.dbo.Discount d LEFT JOIN database2.dbo.DiscountNames dn ON dn.DiscountID=d.DiscountID
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SET @cols2 = STUFF((
	SELECT distinct ', [' + CONVERT(varchar(10),DiscountID) + ']'
	FROM database2.dbo.Discount
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT ItemId, ItemName, ' + @cols1 + ' FROM (
SELECT  I.ItemId, I.ItemName, d.DiscountID, CASE d.Discount WHEN 1 THEN ''Yes'' ELSE ''No'' END as Disc
FROM Database1.dbo.Item I INNER JOIN database2.dbo.Discount d ON I.ItemId = d.ItemId) pv
PIVOT (
   MAX(Disc)
   FOR DiscountID IN (' + @cols2 + ')
 ) AS ItemDiscounts' 
execute(@query)

Select allOpen in new window

If you add a discount without a name then you will get the old Discount # label.