Solved

Help with SQL Query

Posted on 2013-05-10
7
200 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 30

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

Title # Comments Views Activity
SQL Statement to Update Email Domain 2 30
TSQL DateADD update Question 4 40
sql help 8 55
Text file into sql server 5 27
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

809 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