Solved

Help with SQL Query

Posted on 2013-05-10
7
199 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
ID: 39156343
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
ID: 39156372
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
ID: 39156436
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 29

Accepted Solution

by:
Rich Weissler earned 400 total points
ID: 39156559
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
ID: 39156683
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
ID: 39156969
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
ID: 39157160
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 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

15 Experts available now in Live!

Get 1:1 Help Now