Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with SQL Query

Posted on 2013-05-10
7
Medium Priority
?
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 49

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 49

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
Back Up Your Microsoft Windows Server®

Back up 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.

 
LVL 30

Accepted Solution

by:
Rich Weissler earned 1600 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 400 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

660 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