Group Data

Question

Post reply

Group Data

Oren Levy

SSC Veteran

Points: 295
More actions
December 13, 2006 at 6:08 pm

#115397

Hi All
I have a SQL statement that returns 3 values: ItemNumber, Product Line Number, Product Line Description.
(
SELECT dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription
FROM dbo.IM1_InventoryMasterfile INNER JOIN
dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN
dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber
WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude <> 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)
union
SELECT dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription
FROM dbo.Items INNER JOIN
dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode
)
Order By PLDescription, ItemNumber ASC
The data it returns looks like this:
ItemNumber1, PL001, PLDescription1
ItemNumber2, PL001, PLDescription1
ItemNumber3, PL001, PLDescription1
ItemNumber4, PL001, PLDescription1
ItemNumber5, PL002, PLDescription2
ItemNumber6, PL003, PLDescription2
Is there a way to group the data so it will return the data like this:
PL001, PLDescription1
ItemNumber1
ItemNumber2
ItemNumber3
ItemNumber4
PL002, PLDescription3
ItemNumber5
ItemNumber6
Thanks
Oren Levy

Viewing 14 posts - 1 through 13 (of 13 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Simple question:

How many columns you expect in returning resultset?

_____________
Code for TallyGenerator

Oren Levy SSC Veteran Points: 295 More actions · Answer 2

I can live with one column back and disregard the PLnumber (PL001)

Basically

PLDescription1

ItemNumber1

ItemNumber2

ItemNumber3

ItemNumber4

PLDescription2

ItemNumber5

ItemNumber6

Thanks

Oren

Sergiy SSC Guru Points: 110208 More actions · Answer 3

SELECT ReportCol

FROM (

select PLNumber, NULL as SEQUENCE, PLDescription as ReportCol

FROM {YOUR TABLES}

GROUP BY PLNumber, PLDescription

UNION

select PLNumber, ItemNumber, ItemNumber

FROM {YOUR TABLES}

) DT

ORDER BY PLNumber, SEQUENCE

_____________
Code for TallyGenerator

Oren Levy SSC Veteran Points: 295 More actions · Answer 4

Hi Ten Centuries

Thanks for the prompt response.

However I could not make it work, I'm getting an Syntax Error or Access violation.

Here is what what I changed it to:

Probably I'm doing something goofy.

SELECT ReportCol
FROM (
select PL, NULL as SEQUENCE, PLDescription as ReportCol
FROM

{

(
--Select all Items from MAS200 excludeing then ones that are Web Exclude
SELECT     dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription
FROM         dbo.IM1_InventoryMasterfile INNER JOIN
                      dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN
                      dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber

WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude <> 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

--Select all Items SemiStore DB
SELECT     dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription
FROM         dbo.Items INNER JOIN
                      dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode

)

}
GROUP BY PL, PLDescription
UNION
select PL, PLDescription, ItemNumber
FROM
{
(
--Select all Items from MAS200 excludeing then ones that are Web Exclude
SELECT     dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription
FROM         dbo.IM1_InventoryMasterfile INNER JOIN
                      dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN
                      dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber

WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude <> 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

--Select all Items SemiStore DB
SELECT     dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription
FROM         dbo.Items INNER JOIN
                      dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode

)

}
) DT

ORDER BY PL, SEQUENCE

Thanks a lot

Oren Levy

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Remove "{" and "}"

_____________
Code for TallyGenerator

Oren Levy SSC Veteran Points: 295 More actions · Answer 6

Did that but still no luck, I'm getting the following error:

Server: Msg 156, Level 15, State 1, Line 26

Incorrect syntax near the keyword 'GROUP'.

Server: Msg 170, Level 15, State 1, Line 49

Line 49: Incorrect syntax near ')'.

I really appriciate this, thank you very much.

Here is the code:

SELECT ReportCol

FROM (

select PL, NULL as SEQUENCE, PLDescription as ReportCol

FROM

(

--Select all Items from MAS200 excludeing then ones that are Web Exclude

SELECT dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription

FROM dbo.IM1_InventoryMasterfile INNER JOIN

dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN

dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber

WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude <> 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

--Select all Items SemiStore DB

SELECT dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription

FROM dbo.Items INNER JOIN

dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode

)

GROUP BY PL, PLDescription

UNION

select PL, PLDescription, ItemNumber

FROM

(

--Select all Items from MAS200 excludeing then ones that are Web Exclude

SELECT dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription

FROM dbo.IM1_InventoryMasterfile INNER JOIN

dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN

dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber

WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude <> 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

--Select all Items SemiStore DB

SELECT dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription

FROM dbo.Items INNER JOIN

dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode

)

) DT

ORDER BY PL, SEQUENCE

Thanks

Oren

Sergiy SSC Guru Points: 110208 More actions · Answer 7

SELECT ReportCol

FROM (

select PL, NULL as SEQUENCE, PLDescription as ReportCol

FROM

(

--Select all Items from MAS200 excludeing then ones that are Web Exclude

SELECT dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription

FROM dbo.IM1_InventoryMasterfile INNER JOIN

dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN

dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber

WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

--Select all Items SemiStore DB

SELECT dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription

FROM dbo.Items INNER JOIN

dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode

) T1

GROUP BY PL, PLDescription

UNION

select PL, PLDescription, ItemNumber

FROM

(

--Select all Items from MAS200 excludeing then ones that are Web Exclude

SELECT dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription

FROM dbo.IM1_InventoryMasterfile INNER JOIN

dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN

dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber

WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

--Select all Items SemiStore DB

SELECT dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription

FROM dbo.Items INNER JOIN

dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode

) T2

) DT

ORDER BY PL, SEQUENCE

_____________
Code for TallyGenerator

Oren Levy SSC Veteran Points: 295 More actions · Answer 8

Ten Centuries you Rock moch more then 100 decades.

Thank you very much.

Happy Holidays.

Oren

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Hi oren,

FYI, Ten Centuries is a "title" (depends basically on number of posts - e.g. it says "Newbie" at your posts, because you only posted a few times), user name can be found a bit higher, in the post header

Otherwise you're right, Sergiy is one of the true SQL gurus of these forums.

Oren Levy SSC Veteran Points: 295 More actions · Answer 10

Silly me, I guess my excitement with Sergiy Excellence took over.

Thanks Vladan.

Thank you Sergiy again.

Oren Levy SSC Veteran Points: 295 More actions · Answer 11

Sergiy

is there a way to modify it so the sorting will be done by PLDescription instead of by PL and then all the Items ASC.

As a matter of fact I don't need the PL field at all.

so the outcome will be like this:

PLDescription1

ItemNumber1

ItemNumber2

ItemNumber3

ItemNumber4

PLDescription2

ItemNumber5

ItemNumber6

Thanks

Oren

Oren Levy SSC Veteran Points: 295 More actions · Answer 12

Oren Levy

SSC Veteran

Points: 295

December 15, 2006 at 7:03 pm

#678501

Actually I got it.

Thank you anyway.

Oren

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 13

Good Work my friends.........But there is also..........

Another Approch!

Finally I would like to say U can do it with using GROUP BY ....... WITH ROLLUP also. And I think it will not be too big Query for so simple requirement.