Group By (Grouping with Non Aggregrate)

Question

Post reply

Group By (Grouping with Non Aggregrate)

VegasL

Hall of Fame

Points: 3006
More actions
January 25, 2015 at 9:06 am

#296932

Hello,
I have the following test table:
create table #test_table
(KW varchar(100),LP varchar(100))
insert into #test_table
select 'cat' as KW, '/catpage' as LP
insert into #test_table
select 'bigcat' as KW, '/catpage' as LP
insert into #test_table
select 'furrycat' as KW, '/catpage' as LP
insert into #test_table
select 'dog' as KW, '/dogpage' as LP
insert into #test_table
select 'bigdog' as KW, '/dogpage' as LP
insert into #test_table
select 'pets' as KW, '/' as LP
It's Current Result is:
KW LP
cat /catpage
bigcat /catpage
furrycat /catpage
dog /dogpage
big /dogpage
pets /
However, this is not the desired result I am looking for. I am trying to group by LP, so it should read:
LP KW
/catpage cat
big cat
furry cat
/dogpage dog
big dog
/ pets
I tried:
Select LP, KW
from #test_table
group by LP
but this is not working, and I don't want to put a count or sum or any aggregate function, just looking to group without an aggregate function.
Any help would be appreciated.
P.S. I don't know how to edit in forum(place tabs), but the desired result is coming out cluttered without tabs between the columns:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 1

If I understand you needs correctly what about this. create table #test_table

(KW varchar(100),LP varchar(100))

insert into #test_table

select 'cat' as KW, '/catpage' as LP

insert into #test_table

select 'bigcat' as KW, '/catpage' as LP

insert into #test_table

select 'furrycat' as KW, '/catpage' as LP

insert into #test_table

select 'dog' as KW, '/dogpage' as LP

insert into #test_table

select 'bigdog' as KW, '/dogpage' as LP

insert into #test_table

select 'pets' as KW, '/' as lp

SELECT lp, kw

FROM #test_table tt

GROUP BY LP, KW

ORDER BY LP

DROP TABLE #test_table

***SQL born on date Spring 2013:-)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

It's not exactly what you asked for but I have a couple of suggestions on this.

First, this is a "hierarchical" problem but the data you have doesn't exactly support a hierarchical solution without jumping through a code hoop or two. With that thought in mind, I recommend that you structure the data in a true "adjacency list" (parent/child hierarchy) as follows (details are in the comments in the code). This form of the data will also allow for virtually unlimited levels of the data for future growth.

--=============================================================================

-- Create and populate the test table.

-- Note that changes have been made to the table and the data.

--=============================================================================

--===== If the test table already exists, drop it

-- to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Test_Table','U') IS NOT NULL

DROP TABLE #Test_Table

;

GO

--===== Create the test table.

-- Note that KW is the "child" and

-- that LP is the "parent" in this hierarchy.

-- Note also that we got rid of the slashes in

-- the names because that's formatting.

-- 3 rows where also added to make this a proper

-- "Adjacency List" (parent/child) hierarchy.

CREATE TABLE #Test_Table

(

KW VARCHAR(100) NOT NULL --Added NOT NULL

,LP VARCHAR(100)

)

;

--===== Populate the test table

INSERT INTO #Test_Table

(KW,LP)

SELECT 'cat' ,'catpage' UNION ALL

SELECT 'bigcat' ,'catpage' UNION ALL

SELECT 'furrycat' ,'catpage' UNION ALL

SELECT 'dog' ,'dogpage' UNION ALL

SELECT 'bigdog' ,'dogpage' UNION ALL

SELECT 'pets' , NULL UNION ALL --Added row

SELECT 'catpage' ,'pets' UNION ALL --Added row

SELECT 'dogpage' ,'pets' --Added row

;

--===== Add the proper indexing for performance

-- and uniqueness. The "child" MUST be unique.

ALTER TABLE #Test_Table

ADD CONSTRAINT PK_#Test_Table

PRIMARY KEY CLUSTERED (KW)

;

--===== Add the proper FK to ensure that all "parents" (LP)

-- are also "children" (KW).

ALTER TABLE #Test_Table

ADD CONSTRAINT FK_LP_KW FOREIGN KEY

(LP) REFERENCES #Test_Table (KW)

;

--===== Sanity check. See what's in the table.

SELECT * FROM #Test_Table

;

That makes it pretty easy to display the data in a nice hierarchical format along with some other goodies.

--=============================================================================

-- Display the data as a hierarchy

--=============================================================================

WITH cteHierarchy AS

(

SELECT KW, LP, hLevel = 1,

HierarchicalPath = CAST('/'+CAST(KW AS VARCHAR(100)) AS VARCHAR(8000))

FROM #Test_Table

WHERE LP IS NULL

UNION ALL

SELECT e.KW, e.LP, hLevel = d.hLevel + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '/'

+CAST(e.KW AS VARCHAR(100)) AS VARCHAR(8000))

FROM #Test_Table e

INNER JOIN cteHierarchy d ON e.LP = d.KW

)

SELECT LP,

KW = SPACE((hLevel-1)*4) + KW,

hLevel,

HierarchicalPath

FROM cteHierarchy

ORDER BY HierarchicalPath

;

That all results in the following output (and, if you hit "quote" on this post, you'll also see how the results and code were nicely aligned using the [ quote ] IFCode tags).

LP KW hLevel HierarchicalPath

------- ---------------- ------ ------------------------------

NULL pets 1 /pets

pets catpage 2 /pets/catpage

catpage bigcat 3 /pets/catpage/bigcat

catpage cat 3 /pets/catpage/cat

catpage furrycat 3 /pets/catpage/furrycat

pets dogpage 2 /pets/dogpage

dogpage bigdog 3 /pets/dogpage/bigdog

dogpage dog 3 /pets/dogpage/dog

(8 row(s) affected)

The explanation of how all this works can be found in the article at the following link.

http://www.sqlservercentral.com/articles/T-SQL/72503/

As a bit of a sidebar, if you really need to handle hierarchies with a whole lot of elements, consider the methods used in the following article. It takes a bit to understand and setup but makes maintenance of your hierarchies quite simple and not much will beat it for performance (if I do say so myself).

http://www.sqlservercentral.com/articles/Hierarchy/94040/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VegasL Hall of Fame Points: 3006 More actions · Answer 3

Thank you for your help thomashoner.

In the output, the

lp should only one instance each of

/

/catpage

/dogpage

and the kw column should have the corresponding words associated with the page

for example:

/catpage

I've posted a sample result in excel format to clarify.

and on second column for kw it should have

bigcat

cat

funnycat

I dont know how to post a result on forum with tabs to clearly explain...hopefully its a bit more clearer.

hope that makes sense

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Most folks would recommend you use your display application for this:

SELECT

LP = CASE WHEN RN = 1 THEN LP ELSE '' END,

KW

FROM (

SELECT LP, KW, rn = ROW_NUMBER() OVER(PARTITION BY LP ORDER BY (SELECT NULL))

FROM #test_table

) d

ORDER BY LEN(d.LP), d.LP, rn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

VegasL Hall of Fame Points: 3006 More actions · Answer 5

Thanks ChrisM, this worked flawlessly. Now let me try to understand this.

Thank You Again.