Query Help. - 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)

Query Help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-30 : 15:45:59

--SQL 2005.

I need to return CompanyName only once and all the FullName associates with it. Below is the Business rule.
Thank you so much in advance.

IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test
(
CompId INT NULL,
CompanyName VARCHAR(30) NULL,
FullName VARCHAR(30) NULL
)
GO

INSERT INTO #Test VALUES(10, 'Sears', 'John Smith')
INSERT INTO #Test VALUES(10, 'Sears', 'Lisa Smith')

INSERT INTO #Test VALUES(11, 'Target', 'Peter Smith')
INSERT INTO #Test VALUES(11, 'Target', 'Christine Smith')
GO

SELECT *
FROM #Test
go

-- Business Rule: Display CompanyName only once and all the FullName associate with it.

-- Result want:
CompanyName
-----------
Sears -- companyname
John Smith
Lisa Smith
Target --companyname
Peter Smith
Christine Smith

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-30 : 15:59:10

Do you need the FullNames comma separated?
Like the one here...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136357

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-30 : 16:06:35

how about


SELECT [Name] 
  FROM (
	   SELECT DISTINCT CompId, CompanyName AS [Name], 1 AS LineOrder
		 FROM #Test
	UNION ALL
	   SELECT CompId, '    '  + FullName AS [Name], 2 AS LineOrder
		 FROM #Test
) AS XXX
ORDER BY CompId, LineOrder, [Name]

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

Add yourself!
http://www.frappr.com/sqlteam

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-30 : 17:28:03

quote:
Originally posted by X002548

how about

SELECT [Name]
FROM (
SELECT DISTINCT CompId, CompanyName AS [Name], 1 AS LineOrder
FROM #Test
UNION ALL
SELECT CompId, ' ' + FullName AS [Name], 2 AS LineOrder
FROM #Test
) AS XXX
ORDER BY CompId, LineOrder, [Name]

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

Add yourself!
http://www.frappr.com/sqlteam

Thank you so much.

L

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 04:05:16

Similar to this
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/08/06/how-to-display-detailed-data-below-each-header-data.aspx

But this is purely a formation. If you use front end application do the formation there

Madhivanan

Failing to plan is Planning to fail

matrixmind
Starting Member

9 Posts

Posted - 2009-12-01 : 07:31:19

quote:
Originally posted by NguyenL71

--SQL 2005.

I need to return CompanyName only once and all the FullName associates with it. Below is the Business rule.
Thank you so much in advance.

IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test
(
CompId INT NULL,
CompanyName VARCHAR(30) NULL,
FullName VARCHAR(30) NULL
)
GO

INSERT INTO #Test VALUES(10, 'Sears', 'John Smith')
INSERT INTO #Test VALUES(10, 'Sears', 'Lisa Smith')

INSERT INTO #Test VALUES(11, 'Target', 'Peter Smith')
INSERT INTO #Test VALUES(11, 'Target', 'Christine Smith')
GO

SELECT *
FROM #Test
go

-- Business Rule: Display CompanyName only once and all the FullName associate with it.

-- Result want:
CompanyName
-----------
Sears -- companyname
John Smith
Lisa Smith
Target --companyname
Peter Smith
Christine Smith

with ab
as
(
select distinct '<<'+companyname+'>>' as companyname ,CompID,1 as [Tag] From #Test
union all
select FullName,CompID,2 as [Tag] From #Test
)
select companyname From ab order by CompID,tag

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 07:42:24

quote:
Originally posted by matrixmind

quote:
Originally posted by NguyenL71

--SQL 2005.

I need to return CompanyName only once and all the FullName associates with it. Below is the Business rule.
Thank you so much in advance.

IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test
(
CompId INT NULL,
CompanyName VARCHAR(30) NULL,
FullName VARCHAR(30) NULL
)
GO

INSERT INTO #Test VALUES(10, 'Sears', 'John Smith')
INSERT INTO #Test VALUES(10, 'Sears', 'Lisa Smith')

INSERT INTO #Test VALUES(11, 'Target', 'Peter Smith')
INSERT INTO #Test VALUES(11, 'Target', 'Christine Smith')
GO

SELECT *
FROM #Test
go

-- Business Rule: Display CompanyName only once and all the FullName associate with it.

-- Result want:
CompanyName
-----------
Sears -- companyname
John Smith
Lisa Smith
Target --companyname
Peter Smith
Christine Smith

with ab
as
(
select distinct '<<'+companyname+'>>' as companyname ,CompID,1 as [Tag] From #Test
union all
select FullName,CompID,2 as [Tag] From #Test
)
select companyname From ab order by CompID,tag

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

This is just same way of which was already suggested except the usage of cte

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources