CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MDMalik
MDMalik

Reputation: 4001

SQL Server Select Case Statement with two distinct Value in two Columns

I'm trying to do a Join.

From Items Table which has Product Information like ItemID, UpcCode, Facing, ItemNameArabic, ShelfCapacity everything Except SERVICETYPE

Joining to table Schedule which has UpcCode & ServiceType.

As you see in the picture there are two rows for almost the same data except one states the ServiceType (Either VMF or SRR )

What I want is Number of Rows should decrease. If you refer to the picture. NESCAFE GOLD 50G is repeated twice. Once for VMF & SRR. I just want it to appear once by using either approach below

Either it is like 2 Columns [Boolean Columns]

a) VMF Column [If value is VMF then 'Yes' Or Else 'NO']

b) SRR Column [If value is SRR then 'Yes' Or Else 'NO']

Or

One Column [ServiceType]

Where it checks if it has SRR only SRR appears or else Vmf Appears

In any of the above desired result will decrease my number of Rows

Thank You

enter image description here

Upvotes: 0

Views: 15566

Answers (3)

MDMalik
MDMalik

Reputation: 4001

SELECT  case when vmf is null then 0 else 1 end as vmf, 
case when srr is null then 0 else 1 end as srr, 
UpcCode,date, StoreID
FROM
(SELECT itemid,items.UpcCode, ServiceType, date, ROW_NUMBER() OVER (ORDER BY servicetype) AS ROWNUMBER, s.ID as StoreID
           FROM Items join Schedule on Items.upccode= schedule.upccode
) r
PIVOT
(
max (r.rownumber)
FOR r.servicetype IN( [vmf],[srr])
) AS pvt

I would like to thank @nestor for the wonderful answer on link

Upvotes: 1

Gil Peretz
Gil Peretz

Reputation: 2419

You will Not get 1 row per ItemName by dealing only ServiceType column.

Look at your attachment: you have different data in other columns such as Facing and ShelfCapacity.

If you want to get 1 row per Item name you have two options:

  1. remove the different columns from your query.
  2. List item pivot all your different data to columns.

Upvotes: 1

Raghavan
Raghavan

Reputation: 637

I think u cud try changing the ServiceType in the Select statement using a Case Statement

   SELECT  ItemId,....., ServiceType =
          CASE ServiceTypes
             WHEN 'VMF' THEN 'True'
             WHEN 'SRR' THEN 'False'
             ELSE ''
          END, InLimit, IpConcantenated
        from ....
        where ...

If u want a repeat of the column , u cud fetch it twice

   SELECT  ItemId,.....,
          ServiceTypeVMF =
          CASE ServiceType
             WHEN 'VMF' THEN 'True'
             WHEN 'SRR' THEN 'False'
             ELSE ''
          END, 
          ServiceTypeSRR =
          CASE ServiceType
             WHEN 'VMF' THEN 'False'
             WHEN 'SRR' THEN 'True'
             ELSE ''
          END, 
          InLimit,
          IpConcantenated
        from ....
        where ...

well the earlier case statement would fetch u multiple rows but i think this is what should be ur sql statement if u want to convert rows into columns assuming that there are only 2 rows 'VMF' and 'SNR' as u mentioned

with cte as
(select ItemID, Upccode, ..............,
  row_number() over(partition by ItemID, Upccode order by ServiceType) As rn
  ,ServiceType
  ,MinLimit
  ,IsContracted     
from Items)
select ItemID, UpcCode, 
  max(case when rn = 1 then ServiceType end) as ServiceType1,
  max(case when rn = 2 then ServiceType end) as ServiceType2,
  ,MinLimit
  ,IsContracted     
from cte
group by ItemId, UpcCode,.........,MinLimit,IsContracted;

Upvotes: 0

Related Questions