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

Transact-SQL (2005)

TSQL Matrix

Author

Topic

karlmcauley
Starting Member

9 Posts

Posted - 2010-11-02 : 04:46:12

Hi,

I have been banging my head wondering if this was indeed possible using SQL 2005 TSQL. A typical excel based matrix with the same fields in the x and Y axis and then a tick or a cross in the interseeding cells to determine if x cell was compatible with y cell.

I have started off with:

DECLARE @columns VARCHAR(max),
		@query VARCHAR(max)

SELECT	@columns = COALESCE(@columns + ',[' + program_code + ']','[' + program_code+ ']')
FROM	dbo.tbl_NE_Marketing_Programs
GROUP BY program_code


SET @query = '	SELECT *
				FROM 
				(	select distinct program_code, program_code2,Marketing_Program_PK
					from tbl_NE_Marketing_Programs					
				) p
				PIVOT
				(	MAX(Marketing_Program_PK)
					FOR [program_code] IN (' + @columns + ')
				)AS pvt
				order by program_code2'

EXECUTE(@query)

The above code returns:


MP	A	B	C	D	E	F	G
A	25	NULL	NULL	NULL	NULL	NULL	NULL
B	NULL	6	NULL	NULL	NULL	NULL	NULL
C	NULL	NULL	29	NULL	NULL	NULL	NULL
D	NULL	NULL	NULL	28	NULL	NULL	NULL
E	NULL	NULL	NULL	NULL	4	NULL	NULL
F	NULL	NULL	NULL	NULL	NULL	16	NULL
G	NULL	NULL	NULL	NULL	NULL	NULL	7

Where the numeric in the diagonal interseeding cells is the marketing_program_pk that i pivot on. The issue i am having is that this information is to be displayed in a .net gridview and each cell is to be clickable which then assigns two PK's into an intersection table which tells me what marketing programs are compatible with one another.

Is this possible? Am i making sense?

How do i use similar functionality to a pivot but add multiple columns to the pivot so i can get two pks coming back for each cell reference?

Any ideas?

Thanks in advance
Karl

Sachin.Nand

2937 Posts

Posted - 2010-11-02 : 05:00:35

I think you should not be using "program_code2" in the select statement.Does not make sense to me.

PBUH

karlmcauley
Starting Member

9 Posts

Posted - 2010-11-02 : 05:16:57

Hi Sachin.Nand,

Thanks for your response.

Because x and y headers have to be the same and you cannot pivot on the same column i create a program_code2 field in the table but it is an exact replicate of program_code.

Cheers
karl

Sachin.Nand

2937 Posts

Posted - 2010-11-02 : 05:23:29

Can you post some sample data?

PBUH

karlmcauley
Starting Member

9 Posts

Posted - 2010-11-02 : 05:41:40

Hi,


CREATE TABLE [dbo].[tbl_NE_Marketing_Programs](
	[Marketing_Program_PK] [int] IDENTITY(1,1) NOT NULL,
	[Program_Code] [varchar](50) NOT NULL,
	[Program_Code2] [varchar](50) NULL,
	[Program_Group] [varchar](50) NULL	
 CONSTRAINT [PK_tbl_NE_Marketing_Programs] PRIMARY KEY CLUSTERED 
(
	[Marketing_Program_PK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO [tbl_NE_Marketing_Programs]
           ([Program_Code],[Program_Code2],[Program_Group]           
VALUES	('360 VA level 1' ,'360 VA level 1' ,'360 & VA'),
		('360 VA level 2' ,'360 VA level 2' ,'360 & VA'),
		('360 VA level 3' ,'360 VA level 3' ,'360 & VA'),
		('360 VA level 4' ,'360 VA level 4' ,'360 & VA'),
		('360 VA level 4a' ,'360 VA level 4a' ,'360 & VA'),
		('312D @ 0% (3YRS)' ,'312D @ 0% (3YRS)' ,'CFS Focus'),
		('730 - 0% (24 Mths)' ,'730 - 0% (24 Mths)' ,'CFS Focus'),
		('2010 @ 0% (36 Mths)' ,'2010 @ 0% (36 Mths)' ,'CFS Focus'),
		('2% FOR 36 MONTHS' ,'2% FOR 36 MONTHS' ,'CFS Focus')

I have another table called tbl_ne_marketing_programs_compatibility which will store that PK's of those programs that are compatible


CREATE TABLE [dbo].[tbl_NE_Marketing_Programs_Compatibility](
	[Marketing_Program_Compatibility_PK] [int] IDENTITY(1,1) NOT NULL,
	[Marketing_Program_Parent_FK] [int] NOT NULL,
	[Marketing_Program_Compatible_FK] [int] NOT NULL,
CONSTRAINT [tbl_NE_Marketing_Program_Compatibility_PK] PRIMARY KEY CLUSTERED 
(
	[Marketing_Program_Compatibility_PK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources