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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 help in sql

Author  Topic 

inbs
Aged Yak Warrior

860 Posts

Posted - 2014-06-25 : 06:20:41
how i write this in sql

i have a table with two column



PART Order
A 100
B 100
C 100
A 101
B 101
C 102


i want to get a table kind of metrix, the numbers in tables is the numbers of combination of two parts

example:

A- B (Orders : 100,101) - it get 2
A-C (Order: 100) - it get 1
A-A 0 - if it is same part i write 0

result:
[code]
C B A
0 2 0 A
0 2 B
2 1 1 C


gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-25 : 10:07:04
Well, your result doesn't match your specs! The last line should show 0 under column C (since C=C)

Anyway, here's one way to do it:

with orders(part, ordernum) as (
select * from (values
('A', 100),
('B', 100),
('C', 100),
('A', 101),
('B', 101),
('C', 102)
) v(p,o)
),

pivoted as (
select ordernum, A, B, C
from orders o
pivot (count(part) for part in (A, B, C))pvt
)


select * from (
select 0 C, sum(b.B) B, sum(a.A) A, 'C' part
from pivoted c
cross apply (select B from pivoted b where b.ordernum = c.ordernum and C > 0) b
cross apply (select A from pivoted a where a.ordernum = c.ordernum and C > 0) a

union all

select sum(c.C) C, 0, sum(a.A), 'B'
from pivoted b
cross apply (select C from pivoted c where c.ordernum = b.ordernum and B > 0) c
cross apply (select A from pivoted a where a.ordernum = b.ordernum and B > 0) a

union all

select sum(c.C) C, sum(b.B) B, 0, 'A'
from pivoted a
cross apply (select B from pivoted b where b.ordernum = a.ordernum and A > 0) b
cross apply (select C from pivoted c where c.ordernum = a.ordernum and A > 0) c
) q
order by part
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2014-06-25 : 13:53:34
you are right , i have a mistaket in the last line

i have more then 3 parts, how is it changed? (i have 2000 parts)
i want to bring the pairs that have more than 20 times
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-26 : 13:03:06
For more parts, you'll need to do a dynamic query, I believe. That is, using the above as a template, generate the corresponding sql as a string for each part, then put them together and EXEC (@yourquery)

This kind of problem is much easier to do in C# since you can set up a proper 2d array and populate it from query data.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-26 : 15:26:52
I worked up a dynamic version of this. Here's how I did it:

1. Built a list of column names in a variable to use later:


declare @colnames varchar(max) = (
select stuff(x, 1, 2, '')
from (
select ', ' + QUOTENAME(part)
from (select distinct part from #orders) q(part)
order by part
for xml path('')
) x(x)
)

select @colnames


2. Built dynamic sql using the list above to build the pivot table


declare @sql varchar(max) = '
select ordernum, ' + @colnames + '
into ##pivoted
from #orders
pivot (count(part) for part in (' + @colnames + '))pvt
'

select @sql
drop table ##pivoted
exec (@sql)
select * from ##pivoted


3. Built a part names table (with ids) to build a @partsum variable:


declare @parts table(partId int identity(0,1), part varchar(50))
insert into @parts(part)
select distinct part from #orders
select * from @parts

declare @partsum varchar(max) = (
select stuff(x, 1, 2, '')
from (
select ', SUM(q.' + QUOTENAME(part) + ') ' + QUOTENAME(part)
from @parts
order by part
for xml path('')
) x(x)
)

select @partsum


4. Built a query to do one row of the matrix:


declare @sql1 varchar(max) = '
select ' + @partsum + ', ''{col}'' part
from ##pivoted p
cross apply (select ' + @colnames + ' from ##pivoted where ordernum = p.ordernum and {col} > 0) q
'

select @sql1


5. Built the main query with all the unions in it:


declare @sql2 varchar(max) = (
select replace(@sql1, '{col}', QUOTENAME(part)) + ' union all'
from @parts p
join spt_values n
on p.partId = n.number
where n.[type] = 'P'
for xml path('')
)

set @sql2 = replace(replace(@sql2, '#x0D;', ''), '>', '>')
set @sql2 =
'select * from ('
+ substring(@sql2, 1, len(@sql2)-9)
+
') q
order by part'
select @sql2


6. Executed the main query:


exec (@sql2)
Go to Top of Page
   

- Advertisement -