[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

SQL Results, not sure how to get what I want out of them

Hi there,

I'm trying to get results from my SQL code but I can't seem to get them right. Here is my code:

select  tBlocking.WholeCompany, tBlocking.BlockingDate, tSearch.Description, tUser.FirstName + ' ' +  tUser.LastName as Partner, tCompany.CompanyName, tBlocking.BlockingNotes, tPeople.FirstName, tPeople.LastName from tBlocking
LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID
LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID
LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID
LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID
LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID
LEFT JOIN tUser ON tSearchResponsibility.UserGUID = tUser.GUID
LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID
WHERE tBlocking.BlockingDate > GetDate()

I have attached a worksheet with the results I get (yellow) and the ones I want (green).

As you can see the ones in yellow return multiple lines because there are more than one Partner assigned to that company. What I would like to have is when there are more, they show up as they do in green. THere is no max on how many Partners assigned to each company.

How can I get the desired effects?
SQLResultsGood.xls
0
PrimusPilus
Asked:
PrimusPilus
  • 6
  • 3
  • 3
1 Solution
 
aelliso3Commented:
Does each of the partners need to be in a seperate column, or can they be concatenated into one column labled "Partners"
0
 
aelliso3Commented:
Try this:
 

select  tBlocking.WholeCompany
	, tBlocking.BlockingDate
	, tSearch.DESCRIPTION
	, tUser.Partners
	, tCompany.CompanyName
	, tBlocking.BlockingNotes
	, tPeople.FirstName
	, tPeople.LastName 
from tBlocking
	LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID
	LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID
	LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID
	LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID
	LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID
	LEFT JOIN (
			SELECT Main.GUID AS GUID, 
				  LEFT(Main.Partner,Len(Main.Partner)-1) As [Partners] 
			FROM(SELECT DISTINCT b.GUID,  
					 (SELECT a.FirstName + ' ' +  a.LastName + ',' AS [text()] 
					  FROM dbo.tUser a 
					  WHERE a.GUID = b.GUID 
					  ORDER BY a.GUID 
					  FOR XML PATH ('')) [Partner] 
				FROM dbo.tUser b) [Main] 
			) tUser ON tSearchResponsibility.UserGUID = tUser.GUID
	LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID
WHERE tBlocking.BlockingDate > GetDate()

Open in new window

0
 
PrimusPilusAuthor Commented:
Hi,

aelliso, it does not matter, they can all be in the same column!

I still get the same amount of lines with that one, it does not remove duplicate lines for companies.

Thanks for looking into this!
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
aelliso3Commented:
Will SELECT DISTINCT work on the first row?
0
 
PrimusPilusAuthor Commented:
Hi there,

Select Distinct will not work!

0
 
cyberkiwiCommented:
I missed one column, but you can add it back to the pattern.
declare @sql nvarchar(max)  
declare @columns1 varchar(max)
declare @columns2 varchar(max)
declare @maxcount int

select @maxcount=MAX(c) from
(select COUNT(distinct partner) c
 from #tmp
 group by WholeCompany,Description,Company,Notes,FirstName,LastName) x

select
	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',
	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'
from master..spt_values where type='p' and number between 1 and @maxcount

set @sql = '  
	SELECT WholeCompany,Description,Company'+@columns2+',Notes,FirstName,LastName
	FROM (SELECT *, RN=ROW_NUMBER() OVER (
		PARTITION BY WholeCompany,Description,Company,Notes,FirstName,LastName
		ORDER BY PARTNER)
		FROM (SELECT DISTINCT * FROM #TMP) A) B
	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  
EXEC (@sql)  

Open in new window

0
 
PrimusPilusAuthor Commented:
Thanks for the reply cyberiwi. I'm having trouble making sense of your code. What does the #tmp stand for?

I'm not very good with SQL, actually I'm at a basic level, but this looks like something I run after my code?

Thanks!
0
 
cyberkiwiCommented:
You need to put the data into a temp table first.
Create the proc in the code box, and run the singular line below whenever you need to view the data.

exec ViewME;
CREATE PROC ViewME
AS
select  tBlocking.WholeCompany, tBlocking.BlockingDate, tSearch.Description, tUser.FirstName + ' ' +  tUser.LastName as Partner, tCompany.CompanyName, tBlocking.BlockingNotes, tPeople.FirstName, tPeople.LastName
into #tmp
from tBlocking
LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID
LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID
LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID
LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID
LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID
LEFT JOIN tUser ON tSearchResponsibility.UserGUID = tUser.GUID
LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID
WHERE tBlocking.BlockingDate > GetDate();

declare @sql nvarchar(max)  
declare @columns1 varchar(max)
declare @columns2 varchar(max)
declare @maxcount int

select @maxcount=MAX(c) from
(select COUNT(distinct partner) c
 from #tmp
 group by WholeCompany,BlockingDate,Description,Company,Notes,FirstName,LastName) x

select
	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',
	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'
from master..spt_values where type='p' and number between 1 and @maxcount

set @sql = '  
	SELECT WholeCompany, BlockingDate, Description, Company'+@columns2+', Notes, FirstName, LastName
	FROM (SELECT *, RN=ROW_NUMBER() OVER (
		PARTITION BY WholeCompany, BlockingDate, Description, Company, Notes, FirstName, LastName
		ORDER BY PARTNER)
		FROM (SELECT DISTINCT * FROM #TMP) A) B
	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  
EXEC (@sql);
GO

Open in new window

0
 
PrimusPilusAuthor Commented:
Thank you Sir!

I think we are getting somewhere!

I get two error messages:

Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 488, Level 16, State 1, Line 2
Pivot grouping columns must be comparable. The type of column "BlockingNotes" is "text", which is not comparable.

I had to remove the group by line as it gave me this error:

Msg 306, Level 16, State 2, Procedure ViewME, Line 20
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I assumed it might not be vital.....??

Thank you for you help!
0
 
cyberkiwiCommented:
Ignore the null values warning
I assume varchar(100) is enough for BlockingDate?
CREATE PROC ViewME
AS
select  tBlocking.WholeCompany, convert(varchar(100),tBlocking.BlockingDate), tSearch.Description, tUser.FirstName + ' ' +  tUser.LastName as Partner, tCompany.CompanyName, tBlocking.BlockingNotes, tPeople.FirstName, tPeople.LastName
into #tmp
from tBlocking
LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID
LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID
LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID
LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID
LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID
LEFT JOIN tUser ON tSearchResponsibility.UserGUID = tUser.GUID
LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID
WHERE tBlocking.BlockingDate > GetDate();

declare @sql nvarchar(max)  
declare @columns1 varchar(max)
declare @columns2 varchar(max)
declare @maxcount int

select @maxcount=MAX(c) from
(select COUNT(distinct partner) c
 from #tmp
 group by WholeCompany,BlockingDate,Description,Company,Notes,FirstName,LastName) x

select
	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',
	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'
from master..spt_values where type='p' and number between 1 and @maxcount

set @sql = '  
	SELECT WholeCompany, BlockingDate, Description, Company'+@columns2+', Notes, FirstName, LastName
	FROM (SELECT *, RN=ROW_NUMBER() OVER (
		PARTITION BY WholeCompany, BlockingDate, Description, Company, Notes, FirstName, LastName
		ORDER BY PARTNER)
		FROM (SELECT DISTINCT * FROM #TMP) A) B
	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  
EXEC (@sql);
GO

Open in new window

0
 
PrimusPilusAuthor Commented:
Thank you! That worked great!

0
 
PrimusPilusAuthor Commented:
Great help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now