Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display SQL result like Quick Search format Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 4:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:11 AM
Points: 6, Visits: 18
Hello everyone

I want to display the result of SQL result like Quick Search format. For example

Below is the output of my SQL result
Section A (Mick) Albert Edward Scott
Section A Eric aka Mick Vernon Preston
Section A Gary Michael Glenane
Section A Maksymiljan Michael Kaboth
Section A Michael Clarence Braithwaite
Section A Michael Paul Jones
Section A Shayne Michael Currigan
Section A Stipe Mamic
Section B (Mick) Albert Edward Scott
Section B Anthony Michael Payne
Section B Gary Michael Glenane
Section B Maksymiljan Michael Kaboth
Section B Michael Clarence Braithwaite
Section B Michael John Crawford
Section B Michael Paul Jones
Section B Micheal David Joshi
Section B Shayne Michael Currigan
Section B Stipe Mamic

I want the result to display As
Section A
(List of all values whose section are from Section A)
Michael Clarence Braithwaite
Michael Paul Jones
(Mick) Albert Edward Scott
........
Section B
(List of all values whose section are from Section B)
Micheal David Joshi
Shayne Michael Currigan
Stipe Mamic
...........

One way is to save my result in temp table and display it but i know there might be some other better way to perform this..

Please let me know...

Thanx in advance.
Post #1481225
Posted Tuesday, August 6, 2013 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:09 AM
Points: 157, Visits: 163
You could use a recursive CTE instead of temp table. I used a temp table to simulate your result set (and added a couple sections for testing), but you can do this with your base table and not use temp table at all:

IF OBJECT_ID('tempdb..#source') IS NOT NULL
DROP TABLE #source;

CREATE TABLE #source
(Section VARCHAR(255)
,Name VARCHAR(255))

INSERT #source
(Section
,Name)
select 'Section A' Section,'(Mick) Albert Edward Scott' Name
union select 'Section A','Eric aka Mick Vernon Preston'
union select 'Section A','Gary Michael Glenane'
union select 'Section A','Maksymiljan Michael Kaboth'
union select 'Section A','Michael Clarence Braithwaite'
union select 'Section A','Michael Paul Jones'
union select 'Section A','Shayne Michael Currigan'
union select 'Section A','Stipe Mamic'
union select 'Section B','(Mick) Albert Edward Scott'
union select 'Section B','Anthony Michael Payne'
union select 'Section B','Gary Michael Glenane'
union select 'Section B','Maksymiljan Michael Kaboth'
union select 'Section B','Michael Clarence Braithwaite'
union select 'Section B','Michael John Crawford'
union select 'Section B','Michael Paul Jones'
union select 'Section B','Micheal David Joshi'
union select 'Section B','Shayne Michael Currigan'
union select 'Section B','Stipe Mamic'
union select 'Section C','C Test'
union select 'Section D','D Test'
union select 'Section E','E Test';


WITH CTE
(Value,
OrderNum)
AS
(

SELECT
Section AS Value
--Multiply by two so we can add one to the section values and order final result as expected
,ROW_NUMBER() OVER (ORDER BY Section ASC) * 2 AS OrderNum
FROM #source
GROUP BY Section
UNION ALL
SELECT
Name AS Value
,OrderNum + 1
FROM #source
JOIN CTE on #source.Section = CTE.Value
)

SELECT
Value
FROM CTE
ORDER BY OrderNum ASC;

Post #1481463
Posted Tuesday, August 6, 2013 11:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,162, Visits: 12,007
One way is to save my result in temp table and display it but i know there might be some other better way to perform this..


Use a reporting tool. This sort of formatting is generally performed by the client application, not the database server.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1481658
Posted Wednesday, August 7, 2013 10:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:11 AM
Points: 6, Visits: 18
Thanks SSC Rookie

You saved my day
Post #1482135
Posted Thursday, August 8, 2013 6:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 4:57 AM
Points: 141, Visits: 857
While I couldn't agree more with Phil; this sort of operation doesn't belong in the database, I had to try find a way to do it without the UNION - I'm sure there are several ways to accomplish this though;
(needs nicholasdoyle's constructing code)

;WITH CTE0 AS
(
SELECT Section
,NAME
,RowOrdering = ROW_NUMBER() OVER (ORDER BY Section, NAME)
FROM #source
GROUP BY Section, NAME
WITH ROLLUP
)
SELECT VALUE = CASE WHEN NAME IS NULL THEN Section ELSE NAME END
FROM CTE0 c
WHERE Section IS NOT NULL
ORDER BY RowOrdering

Post #1482255
Posted Thursday, August 8, 2013 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:09 AM
Points: 157, Visits: 163
Thanks for showing me a better way diamondgm, I haven't really used WITH ROLLUP before.
Post #1482262
Posted Thursday, August 8, 2013 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 4:57 AM
Points: 141, Visits: 857
nicholasdoyle (8/8/2013)
Thanks for showing me a better way diamondgm, I haven't really used WITH ROLLUP before.


I'm not suggesting it's better, though without indexing, it does seem slightly cheaper
Its a fun exercise!
Post #1482272
Posted Monday, August 19, 2013 7:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
WITH ROLLUP is being deprecated in favor of the ROLLUP() function. Here is the query rewritten using the ROLLUP() function.

;WITH CTE0 AS
(
SELECT Section
,NAME
,RowOrdering = ROW_NUMBER() OVER (ORDER BY Section, NAME)
FROM #source
GROUP BY ROLLUP(Section, NAME)
)
SELECT VALUE = CASE WHEN NAME IS NULL THEN Section ELSE NAME END
FROM CTE0 c
WHERE Section IS NOT NULL
ORDER BY RowOrdering



J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #1485811
Posted Tuesday, August 20, 2013 9:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 4:57 AM
Points: 141, Visits: 857
drew.allen (8/19/2013)
WITH ROLLUP is being deprecated in favor of the ROLLUP() function. Here is the query rewritten using the ROLLUP() function.

;WITH CTE0 AS
(
SELECT Section
,NAME
,RowOrdering = ROW_NUMBER() OVER (ORDER BY Section, NAME)
FROM #source
GROUP BY ROLLUP(Section, NAME)
)
SELECT VALUE = CASE WHEN NAME IS NULL THEN Section ELSE NAME END
FROM CTE0 c
WHERE Section IS NOT NULL
ORDER BY RowOrdering



Thank you drew.allen
Post #1486311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse