Making a query better? - 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

Making a query better?

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-05-18 : 16:16:00

I would say I want to flatten a table but I don't think that's the right term. Maybe it is. Here is my current query, meant to show which users have access to certain tabs in an application:

SELECT 
	Id = w.webuserid,
	Username = s.userid,
	Name = firstname + ' ' + lastname,
	Email = email,
	Active = w.Active,
	Subsidiary = subsidiaryname,
	HumanCapital = CASE WHEN pt.name IS NULL THEN '' ELSE 'X' end,
	Legal = CASE WHEN pt2.name IS NULL THEN '' ELSE 'X' end,
	ITS = CASE WHEN pt3.name IS NULL THEN '' ELSE 'X' end,
	Supplier = CASE WHEN pt4.name IS NULL THEN '' ELSE 'X' end,
	Facility = CASE WHEN pt5.name IS NULL THEN '' ELSE 'X' end,
	Company = CASE WHEN pt6.name IS NULL THEN '' ELSE 'X' end,
	Client = CASE WHEN pt7.name IS NULL THEN '' ELSE 'X' end,
	Operations = CASE WHEN pt8.name IS NULL THEN '' ELSE 'X' end,
	TableF = CASE WHEN pt14.name IS NULL THEN '' ELSE 'X' end,
	Finance = CASE WHEN pt15.name IS NULL THEN '' ELSE 'X' end,
	GrossMargin = CASE WHEN pt16.name IS NULL THEN '' ELSE 'X' end,
	OperationsFinance = CASE WHEN pt17.name IS NULL THEN '' ELSE 'X' end,
	OperationsCallMetrics = CASE WHEN pt18.name IS NULL THEN '' ELSE 'X' end,
	OperationsOther = CASE WHEN pt19.name IS NULL THEN '' ELSE 'X' end

FROM xref_subsidiaryUser s
LEFT OUTER JOIN webusers w ON s.userid = w.username
LEFT OUTER JOIN xref_user_projecttemplates p ON w.webuserid = p.webuserid
LEFT OUTER JOIN vw_info_subsidiary_ccms i ON p.subsidiaryid = i.subsidiaryid
LEFT OUTER JOIN projecttemplate pt ON p.projecttemplateid = pt.projecttemplateid and pt.projecttemplateid=1
LEFT OUTER JOIN projecttemplate pt2 ON p.projecttemplateid = pt2.projecttemplateid and pt2.projecttemplateid=2
LEFT OUTER JOIN projecttemplate pt3 ON p.projecttemplateid = pt3.projecttemplateid and pt3.projecttemplateid=3
LEFT OUTER JOIN projecttemplate pt4 ON p.projecttemplateid = pt4.projecttemplateid and pt4.projecttemplateid=4
LEFT OUTER JOIN projecttemplate pt5 ON p.projecttemplateid = pt5.projecttemplateid and pt5.projecttemplateid=5
LEFT OUTER JOIN projecttemplate pt6 ON p.projecttemplateid = pt6.projecttemplateid and pt6.projecttemplateid=6
LEFT OUTER JOIN projecttemplate pt7 ON p.projecttemplateid = pt7.projecttemplateid and pt7.projecttemplateid=7
LEFT OUTER JOIN projecttemplate pt8 ON p.projecttemplateid = pt8.projecttemplateid and pt8.projecttemplateid=8
LEFT OUTER JOIN projecttemplate pt14 ON p.projecttemplateid = pt14.projecttemplateid and pt14.projecttemplateid=14
LEFT OUTER JOIN projecttemplate pt15 ON p.projecttemplateid = pt15.projecttemplateid and pt15.projecttemplateid=15
LEFT OUTER JOIN projecttemplate pt16 ON p.projecttemplateid = pt16.projecttemplateid and pt16.projecttemplateid=16
LEFT OUTER JOIN projecttemplate pt17 ON p.projecttemplateid = pt17.projecttemplateid and pt17.projecttemplateid=17
LEFT OUTER JOIN projecttemplate pt18 ON p.projecttemplateid = pt18.projecttemplateid and pt18.projecttemplateid=18
LEFT OUTER JOIN projecttemplate pt19 ON p.projecttemplateid = pt19.projecttemplateid and pt19.projecttemplateid=19

The problem is that I get each user listed in a seperate row for each tab they have access to. For example I have Jim in 10 rows: 1 row with an X in the HumanCapital column, another row with an X in the Legal column and so on.

I'd like it to show 1 row for Jim with an X in the appropriate column. How do I make that happen?

Craig Greenwood

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 16:18:35

I think you want webuser to be the driver...FROM WebUser....LEFT JOIN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

craigwg
Posting Yak Master

154 Posts

Posted - 2011-05-18 : 16:26:10

Ok thats a good thought. I made that change, but the result is the same. All users still have one record for each tab.

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 16:28:10

How about:

SELECT 
	Id = w.webuserid,
	Username = s.userid,
	Name = firstname + ' ' + lastname,
	Email = email,
	Active = w.Active,
	Subsidiary = subsidiaryname,
	HumanCapital = CASE WHEN pt.projecttemplateid=1 and pt.name IS NULL THEN '' ELSE 'X' end,
	Legal = CASE WHEN pt.projecttemplateid=2 and pt.name IS NULL THEN '' ELSE 'X' end,
	ITS = CASE WHEN pt.projecttemplateid=3 and pt.name IS NULL THEN '' ELSE 'X' end,
	Supplier = CASE WHEN pt.projecttemplateid=4 and pt.name IS NULL THEN '' ELSE 'X' end,
	Facility = CASE WHEN pt.projecttemplateid=5 and pt.name IS NULL THEN '' ELSE 'X' end,
	Company = CASE WHEN pt.projecttemplateid=6 and pt.name IS NULL THEN '' ELSE 'X' end,
	Client = CASE WHEN pt.projecttemplateid=7 and pt.name IS NULL THEN '' ELSE 'X' end,
	Operations = CASE WHEN pt.projecttemplateid=8 and pt.name IS NULL THEN '' ELSE 'X' end,
	TableF = CASE WHEN pt.projecttemplateid=14 and pt.name IS NULL THEN '' ELSE 'X' end,
	Finance = CASE WHEN pt.projecttemplateid=15 and pt.name IS NULL THEN '' ELSE 'X' end,
	GrossMargin = CASE WHEN pt.projecttemplateid=16 and pt.name IS NULL THEN '' ELSE 'X' end,
	OperationsFinance = CASE WHEN pt.projecttemplateid=17 and pt.name IS NULL THEN '' ELSE 'X' end,
	OperationsCallMetrics = CASE WHEN pt.projecttemplateid=18 and pt.name IS NULL THEN '' ELSE 'X' end,
	OperationsOther = CASE WHEN pt.projecttemplateid=19 and pt.name IS NULL THEN '' ELSE 'X' end
FROM xref_subsidiaryUser s
LEFT OUTER JOIN webusers w ON s.userid = w.username
LEFT OUTER JOIN xref_user_projecttemplates p ON w.webuserid = p.webuserid
LEFT OUTER JOIN vw_info_subsidiary_ccms i ON p.subsidiaryid = i.subsidiaryid
LEFT OUTER JOIN projecttemplate pt ON p.projecttemplateid = pt.projecttemplateid

craigwg
Posting Yak Master

154 Posts

Posted - 2011-05-18 : 16:33:04

Yeah, that's where I started actually. That gives me the same number of rows as the first query, AND every user has EVERY box with an X in it. So, with this query the data is actually incorrect. Its correct the way I had it. I'm just trying to collapse it.

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 17:27:01

This compiles, but I don't know if it's correct:

;WITH PT(Template,ID,X) AS (
	SELECT 'HumanCapital',1,'X' union all select 
	'Legal',2,'X' union all select 
	'ITS',3,'X' union all select 
	'Supplier',4,'X' union all select 
	'Facility',5,'X' union all select 
	'Company',6,'X' union all select 
	'Client',7,'X' union all select 
	'Operations',8,'X' union all select 
	'TableF',14,'X' union all select 
	'Finance',15,'X' union all select 
	'GrossMargin',16,'X' union all select 
	'OperationsFinance',17,'X' union all select 
	'OperationsCallMetrics',18,'X' union all select 
	'OperationsOther',19,'X')
SELECT 
	Id,
	Username,
	Name,
	Email,
	Active,
	Subsidiary,
	HumanCapital=COALESCE(HumanCapital,''),
	Legal=COALESCE(Legal,''), 
	ITS=COALESCE(ITS,''), 
	Supplier=COALESCE(Supplier,''), 
	Facility=COALESCE(Facility,''), 
	Company=COALESCE(Company,''), 
	Client=COALESCE(Client,''), 
	Operations=COALESCE(Operations,''), 
	TableF=COALESCE(TableF,''), 
	Finance=COALESCE(Finance,''), 
	GrossMargin=COALESCE(GrossMargin,''), 
	OperationsFinance=COALESCE(OperationsFinance,''), 
	OperationsCallMetrics=COALESCE(OperationsCallMetrics,''), 
	OperationsOther=COALESCE(OperationsOther,'')
FROM (SELECT 	Id = w.webuserid,
	Username = s.userid,
	Name = firstname + ' ' + lastname,
	Email = email,
	Active = w.Active,
	Subsidiary = subsidiaryname,
	PT.Template, PT.ID, PT.X
FROM xref_subsidiaryUser s
LEFT OUTER JOIN webusers w ON s.userid = w.username
LEFT OUTER JOIN xref_user_projecttemplates p ON w.webuserid = p.webuserid
LEFT OUTER JOIN vw_info_subsidiary_ccms i ON p.subsidiaryid = i.subsidiaryid
LEFT OUTER JOIN PT ON p.projecttemplateid=PT.ID) Z
PIVOT(MAX(Z.X) FOR Z.Template IN (HumanCapital,Legal,ITS,Supplier,Facility,Company,Client,
Operations,TableF,Finance,GrossMargin,OperationsFinance,OperationsCallMetrics,OperationsOther)) b

Since you're not actually using any of the columns from ProjectTemplate I took it out and replaced it with a CTE with the proper values, and added the PIVOT. You can do a regular query with GROUP BY and CASE expressions to pivot the data, but see if that works first.

craigwg
Posting Yak Master

154 Posts

Posted - 2011-05-19 : 09:58:57

I would be lying if I said I understood ALL of that. I got it working enough to produce A result...but I'm not 100% sure it's accurate. Still checking that. When I try to save your CTE as a view it won't let me. Is it not possible to store a CTE as a view? Or another way to ask the same thing, is it not possible to save a view starting with a semi-colon?

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 10:07:47

Yeah, you need to remove the semicolon in a CREATE VIEW definition. It's a habit.

I just used a CTE instead of a temporary table or table variable, the real point was not accessing the ProjectTemplate table since it's not strictly necessary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources