how to group different row values into 1 row

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 2000 Forums

Transact-SQL (2000)

how to group different row values into 1 row

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2006-08-29 : 08:34:23

Hello Guys,

Many thanks for all ur help, all this time, but unfortunately i have one more.
My problem is

I need to group this different values of the same column into a unique value in an another column.

For e.g.

In the query resultset after doing a select i get following values in the "Services" Column

Relo - HS - Standard Prog
Relo - HS - Package Prog
Relo - HS - Purchase Prog
Relo - HS - Orientation

which i have to group them into something like "Relocation Package" as the row value in say column 'Package'.

If by any chance u want to have a look into the original query so here it is.

SELECT
CAST(nlc.name AS nvarchar(255)) AS 'Services',
CAST(nlc.macode AS nvarchar(255)) As 'x',
COUNT (CAST (nlc.name AS nvarchar(50))) AS 'No of Jobs'

INTO #InvoiceRaised

FROM Job As j
LEFT OUTER JOIN SalesInvoice si ON si.job = j.id
LEFT OUTER JOIN SalesInvoiceLines sil ON sil.trannr = si.id
LEFT OUTER JOIN NominalLinkCode nlc ON nlc.id = sil.revenuecode
LEFT OUTER JOIN Modification mod ON mod.id = j.inquiryLogged

WHERE mod.[date] BETWEEN @param1 AND @param2
AND
(sil.revenuecode IN
(
622773258760115409
, -8112270479827337889
, 4054511778913267481
, -3963967039857539357
, 2058088933942448396
, -8319101811152257207
, -4411692570589576061
, -5059595192562757260
, 169193927530118337
, -954181502169738295
, 1353983675601839085
, 5948037925566170771
, 9083867080360900267
, -8905035955639655633
, -6076700602414830421
, 8514601530006219494
, 5444259289906874050
, -3940899285905618252
, 4227262647232361202
, -2317888181912742288
, 1767276902343363157
, -2910916375423623435
, 5149761699620317644
, 2038583595520536865
, 4844040292494250747
, -2688529153590853417
, 6969286315677321060
, -630247784440837013
, -3388491447827960597
, -1793970891278674913
)
)

Group By nlc.name, nlc.macode

SELECT * FROM #InvoiceRaised

DROP TABLE #InvoiceRaised
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-29 : 09:47:39

quote:

WHERE mod.[date] BETWEEN @param1 AND @param2
AND
(sil.revenuecode IN
(
622773258760115409
, -8112270479827337889
, 4054511778913267481
, -3963967039857539357
, 2058088933942448396
, -8319101811152257207
, -4411692570589576061
, -5059595192562757260
, 169193927530118337
, -954181502169738295
, 1353983675601839085
, 5948037925566170771
, 9083867080360900267
, -8905035955639655633
, -6076700602414830421
, 8514601530006219494
, 5444259289906874050
, -3940899285905618252
, 4227262647232361202
, -2317888181912742288
, 1767276902343363157
, -2910916375423623435
, 5149761699620317644
, 2038583595520536865
, 4844040292494250747
, -2688529153590853417
, 6969286315677321060
, -630247784440837013
, -3388491447827960597
, -1793970891278674913
)
)

Good lord ... either store those values in a table somewhere or figure out another common attribute to filter on ... hard-coding "magic numbers" like this into your SQl statements is a very bad practice to get into.

This also applies to your grouping .. you need to create a table of Services (if you don't have one already) and then add a column to indicate how it should be grouped, something like this:

ServiceName,GroupName
Relo - HS - Standard Prog,Relocation Package
Relo - HS - Package Prog,Relocation Package
Relo - HS - Purchase Prog,Relocation Package
Relo - HS - Orientation,Relocation Package
SomeOtherOne, SomeOtherGroup
SomeOtherOn2, SomeOtherGroup
..etc..

Then just join to your table and group on the GroupName column.

Again, it all boils down to have a good database design and storing as much data as you can in existing tables or creating new ones so that you can write your SQL without hardcoding all kinds of data into your code.

If you know that ID's 1,2,3 and 4 all mean something, don't just repeat them over and over in each and every SQL statement that you write; create a table somewhere and indicate this with data.

See: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources