SQL Cross Tab Information 1

[hubbsshaun]

Hi all,

I have written a piece of SQL code that is retrieving active stock numbers from our inventory system and is indicating across the columns if the item is stocked in each of our warehouses. The code currently works, but I am hoping to find a more elegant solution and possibly one that executes more quickly. Any help is greatly appreciated.

Thanks.

Shaun

select distinct 
  sitm.sitm_stock_no,
  case when cen.sitm_status = 'A' then 'X' else null end as cen,
  case when ssc.sitm_status = 'A' then 'X' else null end as ssc,
  case when ach.sitm_status = 'A' then 'X' else null end as ach,
  case when fmc.sitm_status = 'A' then 'X' else null end as fmc,
  case when plc.sitm_status = 'A' then 'X' else null end as plc,
  case when rgh.sitm_status = 'A' then 'X' else null end as rgh,
  case when can.sitm_status = 'A' then 'X' else null end as can,
  case when ccmha.sitm_status = 'A' then 'X' else null end as ccmha,
  case when cgh.sitm_status = 'A' then 'X' else null end as cgh,
  case when hrh.sitm_status = 'A' then 'X' else null end as hrh,
  case when wht.sitm_status = 'A' then 'X' else null end as wht,
  case when sscd.sitm_status = 'A' then 'X' else null end as sscd,
  case when lab.sitm_status = 'A' then 'X' else null end as lab
from stockitem sitm
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 6 ) 
    cen on cen.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 29 ) 
    ssc on ssc.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 3430 ) 
    ach on ach.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 9 ) 
    fmc on fmc.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 7 ) 
    plc on plc.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 8 ) 
    rgh on rgh.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 170 ) 
    can on can.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 149 ) 
    ccmha on ccmha.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 150 ) 
    cgh on cgh.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 171 ) 
    hrh on hrh.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 109 ) 
    wht on wht.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 89 ) 
    sscd on sscd.sitm_stock_no = sitm.sitm_stock_no
  left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 69 ) 
    lab on lab.sitm_stock_no = sitm.sitm_stock_no
where sitm.sitm_status = 'A'

 

[gmmastros]

Instead of all those left joins to the same table, I suggest you put the sitm_sapc condition in the case statement. Furthermore, you don't need to check status = 'A' if you filter on that.

Try this:

select distinct 
  sitm.sitm_stock_no,
  Min(case when sitm_sapc = 6    then 'X' else null end) as cen,
  Min(case when sitm_sapc = 29   then 'X' else null end) as ssc,
  Min(case when sitm_sapc = 3430 then 'X' else null end) as ach,
  Min(case when sitm_sapc = 9    then 'X' else null end) as fmc,
  Min(case when sitm_sapc = 7    then 'X' else null end) as plc,
  Min(case when sitm_sapc = 8    then 'X' else null end) as rgh,
  Min(case when sitm_sapc = 170  then 'X' else null end) as can,
  Min(case when sitm_sapc = 149  then 'X' else null end) as ccmha,
  Min(case when sitm_sapc = 150  then 'X' else null end) as cgh,
  Min(case when sitm_sapc = 171  then 'X' else null end) as hrh,
  Min(case when sitm_sapc = 109  then 'X' else null end) as wht,
  Min(case when sitm_sapc = 89   then 'X' else null end) as sscd,
  Min(case when sitm_sapc = 69   then 'X' else null end) as lab
from stockitem sitm
where sitm.sitm_status = 'A'
      And sitm.sitm_sapc In (6,29,3430,9,7,8,170,149,150,171,109,89,69)
Group By sitm.sitm_stock_no

If you have any questions about this, let me know and I will try to explain it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fxthoth]

Hi George.

First of all I want thank you for your hard work at this forum. I really love read your answers!

I have a question. Why did you use function Min()?

Min(case when sitm_sapc = 6    then 'X' else null end) as cen

may return 'X' or null.

If I try:

select min('X') --return X
select min(null) --return error "Operand data type void type is invalid for min operator."

I understand in Min() function are NULL values ignored. But I don't understand why are nulls in CASE in your SELECT?
Thank you very much.

 

[gmmastros]

I used min and group by to make sure there would only be one row per stock item number.

Let's mock up some sample data.

Select StockId,
       Case When sapc = 6 Then 'X' Else NULL End As [sapc6],
       Case When sapc = 10 Then 'X' Else NULL End As [sapc10],
       Case When sapc = 20 Then 'X' Else NULL End As [sapc20],
       Case When sapc = 30 Then 'X' Else NULL End As [sapc30]
From   @Stock

[tt][blue]
StockId sapc6 sapc10 sapc20 sapc30
----------- ----- ------ ------ ------
1 X NULL NULL NULL
1 NULL X NULL NULL
1 NULL NULL X NULL
2 X NULL NULL NULL
2 NULL X NULL NULL
2 NULL NULL NULL X
[/blue][/tt]

There are 6 rows in the sample data. When you run the code shown above, there are 6 rows in the output. In cross-tab situations like this, you usually want to see a single row returned in the output (for each stock item).

Now, let's look at a similar query, but using min and group by.

Declare @Stock Table(StockId Int, sapc int)

Insert Into @Stock Values(1, 6)
Insert Into @Stock Values(1, 10)
Insert Into @Stock Values(1, 20)
Insert Into @Stock Values(2, 6)
Insert Into @Stock Values(2, 10)
Insert Into @Stock Values(2, 30)

Select StockId,
       Min(Case When sapc = 6 Then 'X' Else NULL End) As [sapc6],
       Min(Case When sapc = 10 Then 'X' Else NULL End) As [sapc10],
       Min(Case When sapc = 20 Then 'X' Else NULL End) As [sapc20],
       Min(Case When sapc = 30 Then 'X' Else NULL End) As [sapc30]
From   @Stock
Group By StockId

This time, the output looks like:

[tt][blue]
StockId sapc6 sapc10 sapc20 sapc30
----------- ----- ------ ------ ------
1 X X X NULL
2 X X NULL X
[/blue][/tt]

This is probably how hubbsshaun wants to see the data. Of course, I'm just guessing about the format of the output, so I could be wrong.

Most (if not all) of the aggregate functions ignore NULLS. I know for sure that Min, Max, Sum, Count, Avg (and probably others) do.

Truth is, it didn't matter which aggregate function I used because the important part about the aggregate function is to ignore null values. In this case, we are using the value 'X', so I wouldn't use SUM, or AVG, but MIN or MAX would do just fine.

Let's take a closer look at the results of the first query (with 6 rows)...

[tt]
StockId sapc6 sapc10 sapc20 sapc30
----------- ----- ------ ------ ------
1 [!]X[/!] NULL NULL [blue]NULL[/blue]
1 [!]NULL[/!] X NULL [blue]NULL[/blue]
1 [!]NULL[/!] NULL X [blue]NULL[/blue]

2 X NULL NULL NULL
2 NULL X NULL NULL
2 NULL NULL NULL X
[/tt]

Notice the 3 values in red. We have X, NULL, and NULL. By grouping on the StockId and using the MIN (or MAX) function, the result would be X.

Now, notice the data shown in blue. Since all of the values in that column (for stockid = 1) are NULL, the MIN is also NULL.

Does this make sense now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I don't understand why are nulls in CASE in your SELECT

Ah... one more thing. The NULLs in the select part are not needed. In a Case statement, if there is no match, the result will be NULL be default. I left them in because that's what was in the original code. In my own coding, I would also have included the 'else null' part because I think it is a little more obvious about what the return value is. Since the return value is NULL when the when part of a case statement does not match, it isn't needed. Really, this is a style thing.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fxthoth]

Yes, it makes sense . I tried your code in Management studio and I completely understand what you explained.

Thank you very much.
Star for you.

 

[hubbsshaun]

Thanks very much George for your solution and explanations. Everything is working great with your much more concise piece of code.

Shaun

 

[gmmastros]

hubbsshaun,

I just noticed that the query I suggested has a DISTINCT operator. You don't need that anymore. By removing the DISTINCT, you will get the same results, but perhaps a slightly more efficient query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom