pseudo columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pseudo columns

Author

Topic

Kulwant
Starting Member

4 Posts

Posted - 2013-03-06 : 02:35:00

I Have a Unit table with two fields: Unit and Description


Unit   Description
A      This is unit A
B      This is unit B
C      This is unit C

I have another Parts table having three fields: parts UnitID Available

parts UnitID Available
part1   A       1
part2   A       1
part1   B       1
part2   B       0
part3   B       1
part1   C       0
part2   C       0
part3   C       0

If all the parts for a unit is available then only the unit should be available otherwise not. Also, I want to show that is a unit is not available then which which part is not available

So, I want output with pseudo columns as :


Unit     Description      Available    part1   part2   part3
A      This is unit A      1              1       1          
B      This is unit B      0              1       0       1
C      This is unit C      0              0       0       0

Any help will be appreciated. thanks

shaggy
Posting Yak Master

248 Posts

Posted - 2013-03-06 : 03:42:43

This might help you

create table tab1
(
unit char(1),
Description varchar (30)
)

create table tab2
(
parts varchar(5),
unit char(1),
available int
)

--truncate table tab1
insert tab1
select 'A','This is unit A'
union select 'B','This is unit B'
union select 'C','This is unit C'

insert tab2
select 'part1','A',1
union select 'part2','A',1
union select 'part1','B',1
union select 'part2','B',0
union select 'part3','B',1
union select 'part1','C',0
union select 'part2','C',0
union select 'part3','C',0

select unit,Description,
case when part1 = 0 or part2 = 0 or part3 = 0 then 0 else 1 end available,
part1,part2,part3
from (
select a.unit,Description,parts,available
from tab1 a
inner join tab2 b on a.unit = b.unit ) a
pivot (sum(available) for parts in (part1,part2,part3)) as t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 03:44:05

[code]
SELECT u.Unit,u.Description,CASE WHEN p.NACnt > 0 THEN 0 ELSE 1 END AS Available,part1,part2,part3
FROM Unit u
INNER JOIN (SELECT UnitID,
MAX(CASE WHEN parts='part1' THEN Available END) AS part1,
MAX(CASE WHEN parts='part2' THEN Available END) AS part2,
MAX(CASE WHEN parts='part3' THEN Available END) AS part3,
SUM(CASE WHEN Available=0 THEN 1 ELSE 0 END) AS NACnt
FROM Parts
GROUP BY unitID
)p
On p.unitID = u.Unit
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kulwant
Starting Member

4 Posts

Posted - 2013-03-06 : 04:14:36

Thanks alot ... :) .... This was the very first time i ever asked a question through a forum.

Thank you all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 04:38:50

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kulwant
Starting Member

4 Posts

Posted - 2013-03-14 : 09:06:50

The situation is bit more complex now:


Unit   Description
A      This is unit A
B      This is unit B
C      This is unit C
D      This is unit D

parts UnitID Available
part1   A       1
part2   A       1
part1   B       1
part2   B       0
part3   B       1
part1   C       0
part2   C       1
part3   C       1
part1   D       1
part2   D       0
part3   D       0

The rule is different for each unit.
For unit A to be available, part1 or part2 should be available
For unit B to be available, part1 or part2 should be available and part3 should be available
For unit C to be available part1 and part2 should be available
For unit D to be available, part1 or part2 should be available 

So, I want output with pseudo columns as:

Unit     Description      Available    part1   part2   part3
A      This is unit A      1              1       1          
B      This is unit B      1              1       0       1
C      This is unit C      0              0       1       1
D      This is unit D      1              1       0       0 

But I also want to know that how I will store this information in database for each unit and use it to generate the output as above. 

So, I am looking for database schema first to hold the information about the rules for each unit and then the query to generate the output.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources