August 6, 2013 at 4:31 am
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 AEric aka Mick Vernon Preston
Section AGary Michael Glenane
Section AMaksymiljan Michael Kaboth
Section AMichael Clarence Braithwaite
Section AMichael Paul Jones
Section AShayne Michael Currigan
Section AStipe Mamic
Section B(Mick) Albert Edward Scott
Section BAnthony Michael Payne
Section BGary Michael Glenane
Section BMaksymiljan Michael Kaboth
Section BMichael Clarence Braithwaite
Section BMichael John Crawford
Section BMichael Paul Jones
Section BMicheal David Joshi
Section BShayne Michael Currigan
Section BStipe 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.
August 6, 2013 at 11:16 am
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;
August 6, 2013 at 11:42 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 7, 2013 at 10:58 pm
Thanks SSC Rookie
You saved my day
August 8, 2013 at 6:16 am
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
August 8, 2013 at 6:24 am
Thanks for showing me a better way diamondgm, I haven't really used WITH ROLLUP before.
August 8, 2013 at 6:33 am
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!
August 19, 2013 at 7:34 am
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
August 20, 2013 at 9:21 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply