My head hurts, is this possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

My head hurts, is this possible?

Author

Topic

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-05 : 08:38:29

How can I turn the following into a matrix?


a	b	c	d	e	f	g
1	1	0	3	6	2	0
0	1	0	2	0	0	0
0	1	0	0	0	0	0
2	1	0	0	0	0	1

What I want to achieve is:


	a	b	c	d	e	f	g
a	2	2	0	1	1	1	1
b	2	4	0	2	1	1	1
c	0	0	0	0	0	0	0
d	1	2	0	1	1	1	0
e	1	1	0	1	1	1	0
f	1	1	0	1	1	1	0
g	1	1	0	0	0	0	1

This is doing my head in currently. I have gotten to the first result using a pivot on the original data, but can not work out where to go from here.

Thanks for any help.


declare @a table (a int,b int, c int, d int, e int, f int, g int)

insert into @a
select 1,1,0,3,6,2,0
union select 0,1,0,2,0,0,0
union select 0,1,0,0,0,0,0
union select 2,1,0,0,0,0,1

I thought about doing this with a cursor, but want a set-based way if possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 10:12:09

Whats the rule for getting those values in matrix?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 10:25:56

You want to multiply matrix N with transposed matrix N?

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-05 : 11:27:29

Yeah, used a cursor in the end. Its quick enough and works. It is for product cross sales reporting to see which products are worth keeping on a recent acquisition.

Thanks anyway.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-02-05 : 11:59:55

I still haven't a clue what the relationship between those two things is supposed to be.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-06 : 02:16:46

Ok, i'll explain it..

If there is a value in a then that is a count of 1, no matter what the value is, so a to a has 2 values in the above example. Now, 2 people who have brought b have also brought a so a to b is 2, but b to b is 4 as 4 people brought b.

It is used to see how many customers are cross buying product lines, so the company I am working for knows which ones it needs to keep. Took me a while to get my head around the logic though.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-06 : 02:45:18

If anyone can find a set-based way to do this, then i'd love to see it. I'm sure there must be one.


declare @tab1 table (a int,b int, c int, d int, e int, f int, g int)

insert into @tab1
select 1,1,0,3,6,2,0
union select 0,1,0,2,0,0,0
union select 0,1,0,0,0,0,0
union select 2,1,0,0,0,0,1

declare @tab2 table (bid int identity(1,1), [type] char(1),a int,b int, c int, d int, e int, f int, g int)

insert into @tab2 values ('a',0,0,0,0,0,0,0)
insert into @tab2 values ('b',0,0,0,0,0,0,0)
insert into @tab2 values ('c',0,0,0,0,0,0,0)
insert into @tab2 values ('d',0,0,0,0,0,0,0)
insert into @tab2 values ('e',0,0,0,0,0,0,0)
insert into @tab2 values ('f',0,0,0,0,0,0,0)
insert into @tab2 values ('g',0,0,0,0,0,0,0)

declare @a int, @b int, @c int, @d int, @e int, @f int, @g int

declare mcur cursor for
	select a,b,c,d,e,f,g from @tab1

open mcur

fetch next from mcur into @a, @b, @c, @d, @e, @f, @g

while @@fetch_status = 0
	begin
		if @a <> 0
			begin
				update @tab2 set a = a+1 where [type] = 'a'
				if @b <> 0
					begin
						update @tab2 set a = a+1 where [type] = 'b'
						update @tab2 set b = b+1 where [type] = 'b'
						update @tab2 set b = b+1 where [type] = 'a'
					end
				if @c <> 0
					begin
						update @tab2 set a = a+1 where [type] = 'c'
						update @tab2 set c = c+1 where [type] = 'c'
						update @tab2 set c = c+1 where [type] = 'a'
					end
				if @d <> 0
					begin
						update @tab2 set a = a+1 where [type] = 'd'
						update @tab2 set d = d+1 where [type] = 'd'
						update @tab2 set d = d+1 where [type] = 'a'
					end
				if @e <> 0
					begin
						update @tab2 set a = a+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'a'
					end
				if @f <> 0
					begin
						update @tab2 set a = a+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'a'
					end
				if @g <> 0
					begin
						update @tab2 set a = a+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'a'
					end
			end

		if @b <> 0 and @a = 0
			begin
				update @tab2 set b = b+1 where [type] = 'b'
				if @c <> 0
					begin
						update @tab2 set b = b+1 where [type] = 'c'
						update @tab2 set c = c+1 where [type] = 'c'
						update @tab2 set c = c+1 where [type] = 'b'
					end
				if @d <> 0
					begin
						update @tab2 set b = b+1 where [type] = 'd'
						update @tab2 set d = d+1 where [type] = 'd'
						update @tab2 set d = d+1 where [type] = 'b'
					end
				if @e <> 0
					begin
						update @tab2 set b = b+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'b'
					end
				if @f <> 0
					begin
						update @tab2 set b = b+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'b'
					end
				if @g <> 0
					begin
						update @tab2 set b = b+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'b'
					end
			end

		if @c <> 0 and @a = 0 and @b = 0
			begin
				update @tab2 set c = c+1 where [type] = 'c'
				if @d <> 0
					begin
						update @tab2 set c = c+1 where [type] = 'd'
						update @tab2 set d = d+1 where [type] = 'd'
						update @tab2 set d = d+1 where [type] = 'c'
					end
				if @e <> 0
					begin
						update @tab2 set c = c+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'c'
					end
				if @f <> 0
					begin
						update @tab2 set c = c+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'c'
					end
				if @g <> 0
					begin
						update @tab2 set c = c+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'c'
					end
			end		

		if @d <> 0 and @c = 0 and @a = 0 and @b = 0
			begin
				update @tab2 set d = d+1 where [type] = 'd'
				if @e <> 0
					begin
						update @tab2 set d = d+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'e'
						update @tab2 set e = e+1 where [type] = 'd'
					end
				if @f <> 0
					begin
						update @tab2 set d = d+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'd'
					end
				if @g <> 0
					begin
						update @tab2 set d = d+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'd'
					end
			end

		if @e <> 0 and @d = 0 and @c = 0 and @a = 0 and @b = 0
			begin
				update @tab2 set e = e+1 where [type] = 'e'
				if @f <> 0
					begin
						update @tab2 set e = e+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'f'
						update @tab2 set f = f+1 where [type] = 'e'
					end
				if @g <> 0
					begin
						update @tab2 set e = e+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'e'
					end
			end

		if @f <> 0 and @e = 0 and @d = 0 and @c = 0 and @a = 0 and @b = 0
			begin
				update @tab2 set f = f+1 where [type] = 'f'
				if @g <> 0
					begin
						update @tab2 set f = f+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'g'
						update @tab2 set g = g+1 where [type] = 'f'
					end
			end

		if @g <> 0 and @f = 0 and @e = 0 and @d = 0 and @c = 0 and @a = 0 and @b = 0
			begin
				update @tab2 set g = g+1 where [type] = 'g'
			end

		fetch next from mcur into @a, @b, @c, @d, @e, @f, @g
	end

close mcur
deallocate mcur

select [type],a,b, c, d, e, f, g from @tab2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 03:30:50

You can optimize your algorithm above to only halt the iterations.

Cell (i,j) has the same value as cell (j,i).
Draw a "line" diagonally between aa and gg, and you will see that they are each others mirrors.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 03:59:45

Strange, I get D->D as 2, not 1 as in your expected output...

Type	A	B	C	D	E	F	G
A	2	2	0	1	1	1	1
B	2	4	0	2	1	1	1
C	0	0	0	0	0	0	0
D	1	2	0	2	1	1	0
E	1	1	0	1	1	1	0
F	1	1	0	1	1	1	0
G	1	1	0	0	0	0	1

But I am confused. Your CURSOR approach gives this result

type	a	b	c	d	e	f	g
a	2	2	0	1	1	1	1
b	2	4	0	1	0	0	0
c	0	0	0	0	0	0	0
d	1	1	0	2	0	0	0
e	1	0	0	0	1	0	0
f	1	0	0	0	0	1	0
g	1	0	0	0	0	0	1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 04:37:46

I think this is the smallest amount of code I can come up with

All your original source tables are intact.

-- Prepare CTE
;WITH Yak (RowID, A, B, C, D, E, F, G)
AS (
	SELECT	ROW_NUMBER() OVER (ORDER BY A),
		SIGN(A),
		SIGN(B),
		SIGN(C),
		SIGN(D),
		SIGN(E),
		SIGN(F),
		SIGN(G)
	FROM	@Tab1
)

-- Show the expected output
SELECT		u.[Type],
		SUM(u.p * v.A) AS A,
		SUM(u.p * v.B) AS B,
		SUM(u.p * v.C) AS C,
		SUM(u.p * v.D) AS D,
		SUM(u.p * v.E) AS E,
		SUM(u.p * v.F) AS F,
		SUM(u.p * v.G) AS G
FROM		Yak AS s
UNPIVOT		(
				p
			FOR	[Type] IN (s.[A], s.[B], s.[C], s.[D], s.[E], s.[F], s.[G])
		) AS u
INNER JOIN	Yak AS v ON v.RowID = u.RowID
GROUP BY	u.[Type]
ORDER BY	u.[Type]

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-06 : 04:58:33

Thanks Peso, that's brilliant.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 05:45:00

I would hate to disappoint Jon
http://weblogs.sqlteam.com/jhermiz/archive/2007/12/17/What-If-The-Dream-Company.aspx

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-06 : 06:25:58

I'm sure you wouldn't.

But you do seem to have too much time on your hands, thank god.

P.S. Thanks for the add on linkedin.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-02-06 : 06:49:23

Ah, the *sign* of the value.

I like that code a lot, but it does rely on both Yak CTE references getting their rows labelled the same RowID value. I can't really see why they wouldn't, but ROW_NUMBER() is non-deterministic if there isn't a unique ordering of values. If you used
ROW_NUMBER() OVER (ORDER BY a, b, c, d, e, f, g) it would guarantee that rows in each Yak CTE reference had the same values for a given RowID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 06:59:22

That's true. I stand corrected.
Here is a variant with buckets and belts

-- Prepare CTE
;WITH Yak (RowID, A, B, C, D, E, F, G)
AS (
	SELECT	ROW_NUMBER() OVER (ORDER BY A, B, C, D, E, F, G),
		SIGN(A),
		SIGN(B),
		SIGN(C),
		SIGN(D),
		SIGN(E),
		SIGN(F),
		SIGN(G)
	FROM	@Tab1
)

-- Show the expected output
SELECT		u.[Type],
		SUM(u.p * v.A) AS A,
		SUM(u.p * v.B) AS B,
		SUM(u.p * v.C) AS C,
		SUM(u.p * v.D) AS D,
		SUM(u.p * v.E) AS E,
		SUM(u.p * v.F) AS F,
		SUM(u.p * v.G) AS G
FROM		Yak AS s
UNPIVOT		(
			p
			FOR	[Type] IN (s.[A], s.[B], s.[C], s.[D], s.[E], s.[F], s.[G])
		) AS u
INNER JOIN	Yak AS v ON v.RowID = u.RowID
GROUP BY	u.[Type]
ORDER BY	u.[Type]

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-06 : 07:22:32

Yes, after running the original, it didn't quite nmatch up, but with this change it works perfectly.

Thanks again.

Peter - Hope the recommendation was OK.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 18:34:07

Yes, it is excellent. Thank you.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources