help in sql - 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

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

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

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.

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources