SQL Groupby

Hello
I have the below query which is providing me the correct output but i want the output in a proper format sich i am attaching in two sheets of the excel .

Please help

TIA Format.xlsx
select 
IM.inventorycontrolid,IM.Name1,EM.espid,EM.serialnumber,CM.companyname,IM.model,IM.quantityinstore
,CLM.caliberationdate,CLM.expirydate,CLM.certificatenumber,CM1.companyname,CLM.Remarks
from dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5

Open in new window

LVL 2
ExpertHelp79Asked:
Who is Participating?
 
BitsqueezerCommented:
Hi,

if your output table looks exactly like the one you posted in your Excel file above you can merge the cells with this little macro:

Public Sub MergeResults()
    Dim objRng As Excel.Range
    Dim objCell As Excel.Range
    Dim i As Long, k As Long
    Dim varCurrentValue As Variant
    Dim varLastValue As Variant
    
    Application.DisplayAlerts = False
    Set objRng = ActiveSheet.UsedRange
    varLastValue = ""
    For i = 1 To objRng.Rows.Count - 1
        varCurrentValue = objRng.Cells(1 + i, 1)
        If varCurrentValue <> varLastValue Then
            If varLastValue <> "" Then
                'Debug.Print "Merge " & k & "," & i - 1 & " " & varLastValue & "," & varCurrentValue
                With objRng.Range(Cells(k + 1, 1), Cells(i, 1))
                    .MergeCells = True
                    .VerticalAlignment = xlCenter
                End With
                With objRng.Range(Cells(k + 1, 6), Cells(i, 6))
                    .MergeCells = True
                    .VerticalAlignment = xlCenter
                End With
            End If
            varLastValue = varCurrentValue
            k = i
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Open in new window


Copy it to a standard module and create a button or something like that to start it - or start it directly with ALT-F8 and choose "MergeResults" from the list.

Cheers,

Christian
0
 
hyphenpipeCommented:

select 
IM.Name1,IM.inventorycontrolid,EM.espid,EM.serialnumber,CM.companyname,IM.model,sum(IM.quantityinstore)
,CLM.caliberationdate,CLM.expirydate,CLM.certificatenumber,CM1.companyname,CLM.Remarks
from dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5
group by IM.Name1, IM.inventorycontrolid, EM.espid, EM.serialnumber, CM.companyname, IM.model, CLM.caliberationdate,CLM.expirydate, CLM.certificatenumber,CM1.companyname,CLM.Remarks

Open in new window

0
 
ExpertHelp79Author Commented:
same result as the CURRENT... Ineed the format as REQUIRED
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Alpesh PatelAssistant ConsultantCommented:
Which type of format, Cell color or Text alignment format?
0
 
ExpertHelp79Author Commented:
i coloured the column just to indicate the change ...

Column A and Column F need to be changed

Column A: merge the recurring items and Column F add the number for recurring items in Column A and then Merge . I need the same output as the REQUIRED sheet
0
 
BitsqueezerCommented:
Hi,

you cannot do this with a query. The "GROUP BY" clause exists to aggregate some rows of data and give you the result. What you can do is: you can create a group sum for each name ("Test1" - "Test5") with:
SELECT IM.Name1,SUM(IM.quantityinstore) AS SumOfQty
FROM dbo.inventorycontrol_master IM
WHERE IM.categoryid = 5
GROUP BY IM.Name1

Open in new window


This would give you the result of your two yellow columns. But as these are aggregates (sum in this case) you lost any detail information (which you normally don't need here, as it is a sum).

What you also can do is: You can create a detail list and attach an additional column with the aggregated sum for each line like this:

WITH qryNameSums AS
(
SELECT IM.Name1 AS Name2,SUM(IM.quantityinstore) AS SumOfQty
FROM dbo.inventorycontrol_master IM
WHERE IM.categoryid = 5
GROUP BY IM.Name1
)
SELECT	IM.inventorycontrolid,IM.Name1,
		EM.espid,EM.serialnumber,
		CM.companyname,IM.model,
		IM.quantityinstore,CLM.caliberationdate,
		CLM.expirydate,CLM.certificatenumber,
		CM1.companyname,CLM.Remarks,
		(SELECT SumOfQty FROM qryNameSums WHERE qryNameSums.Name2 = IM.Name1) AS SumOfNameQty
FROM	dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5

Open in new window


But you will of course get the sum in every row of the result.

You can NOT change any format inside of Excel with the query, it is not possible to use SQL to merge cells or color cells and so on. If you want this result you would need additional VBA which loops through the result table and changes the format of the cells of one group to merged.

Cheers,

Christian
0
 
8080_DiverCommented:
Y'all GROUP BY is being misused in your queries!!!!

What is needed is an ORDER BY clause!  GROUP BY is used with aggregations!

See attached code:
SELECT IM.inventorycontrolid
  ,IM.Name1
  ,EM.espid
  ,EM.serialnumber
  ,CM.companyname
  ,IM.model
  ,IM.quantityinstore
  ,CLM.caliberationdate
  ,CLM.expirydate
  ,CLM.certificatenumber
  ,CM1.companyname
  ,CLM.Remarks
FROM dbo.inventorycontrol_master IM
LEFT OUTER JOIN dbo.equipment_master EM
ON  IM.inventorycontrolid = EM.inventorycontrolid
LEFT OUTER JOIN dbo.company_master CM
ON  IM.manufacturerid = CM.companyid
LEFT OUTER JOIN dbo.caliberationrecord_master CLM
ON  EM.equipmentid = CLM.equipmentid
LEFT OUTER JOIN dbo.company_master CM1
ON  CLM.vendorid = CM1.companyid
WHERE IM.categoryid = 5
ORDER BY IM.Name1, EM.espid;

Open in new window

0
 
BitsqueezerCommented:
@8080_Diver:
OK, and how do you get the sum of the names groups "Test1" to "Test5" without using SUM (which is an aggregate) and GROUP BY? Show me how to do this with ORDER BY...:-)

Cheers,

Christian
0
 
8080_DiverCommented:
@Christian,

I see no aggregation in the author's query.

I have the below query which is providing me the correct output but i want the output in a proper format sich i am attaching in two sheets of the excel .
select 
IM.inventorycontrolid,IM.Name1,EM.espid,EM.serialnumber,CM.companyname,IM.model,IM.quantityinstore
,CLM.caliberationdate,CLM.expirydate,CLM.certificatenumber,CM1.companyname,CLM.Remarks
from dbo.inventorycontrol_master IM

Left outer join dbo.equipment_master EM on IM.inventorycontrolid = EM.inventorycontrolid

Left outer join dbo.company_master CM on IM.manufacturerid = CM.companyid

Left outer join dbo.caliberationrecord_master CLM on EM.equipmentid = CLM.equipmentid

Left outer join dbo.company_master CM1 on CLM.vendorid = CM1.companyid

Where IM.categoryid = 5

Open in new window

0
 
BitsqueezerCommented:
OK, but you wrote "Y'all ...in your queries" and I read "Y'all" as "you all" - correct me, if I'm wrong. Moreover the author also posted an Excel file which shows which result is expected, so where could an "ORDER BY" help in this case?...

Cheers,

Christian
0
 
ExpertHelp79Author Commented:
Bitsqueezer:
 
Hi,
your query is returning the result but how to merge the duplicate rows in column Name1 and sumofnameqty
0
 
ExpertHelp79Author Commented:
Christian
Thanks for the help... but i need to bind it to a gridview in asp.net
0
 
BitsqueezerCommented:
Hi,

in ASP.NET you could manipulate a HTML table so you have a lot more to do as there is no merge command like in Excel. As you can create nested tables with HTML you could create one table with the group query (the first in my examples above) so you have three columns, one for the names, one empty and one for the sum. In the end you can fill each of the empty cells with the detail rows in x new tables which you insert into these cells.
I don't know if it is possible to create vertical merged cells in a grid view like Excel or HTML can, but I would guess it can't.

I'm afraid I cannot help you with a script in ASP.NET, for this you could maybe create a new thread, I can only help you with SQL Server questions or Office programming.

Cheers,

Christian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.