Solved

Help with SQL Query

Posted on 2013-05-10
7
198 Views
Last Modified: 2013-05-10
Experts,

Details have been attached. Please correct/update my script

Thanks in advance
Details.docx
0
Comment
Question by:Tpaul_10
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Open in new window

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

Expert Comment

by:Beartlaoi
Comment Utility
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)

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Accepted Solution

by:
Rich Weissler earned 400 total points
Comment Utility
Overkill, but playing with PIVOT, just 'cause...

select  ItemID, ItemName, [1] as [Discount 1], [2] as [Discount 2],[3] as [Discount 3], [4] as [Discount 4], [5] as [Discount 5] from
(
   Select  i.itemid, i.itemname, d.discountid, case d.discount when 0 then 'No' when 1 then 'Yes' end as discount
     from item i 
     join discount d on i.itemid = d.itemid
 ) ItemDiscount
PIVOT (
   max(discount)
   FOR discountid IN ([1],[2],[3],[4],[5])
 ) as outputtable

Open in new window


I personally think it's a little cleaner because it moves the Discount 1, Discount 2, etc out of the rows and into the columns and gives a single column for each discount.  I apologize in advance if that was undesirable.
0
 
LVL 9

Assisted Solution

by:Beartlaoi
Beartlaoi earned 100 total points
Comment Utility
If you want the one column per discount layout, here is a dynamic pivot
DECLARE @cols1 AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols1 = STUFF((
	SELECT distinct ', [' + CONVERT(varchar(10),DiscountID) + '] as [Discount ' + CONVERT(varchar(10),DiscountID) + ']'
	FROM database2.dbo.Discount
    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)

Open in new window

0
 

Author Comment

by:Tpaul_10
Comment Utility
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
0
 
LVL 9

Expert Comment

by:Beartlaoi
Comment Utility
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)

Open in new window

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now