To display select statement - sql query

Question

I have a query that selects companyName and department from the database. When I display on my website, it will be order by companyName.

When I add select productSold from the department, my department got separated into 2 records if there are 2 productSold. Is there anyway I can display 1 department with 2 records in the table. Thanks.

My query is as follows:

SELECT dbo.CompanyAdd.companyName, 
       dbo.OrderDetails.department,
       dbo.OrderDetails.productSold
FROM   dbo.CompanyAdd 
INNER JOIN
       dbo.OrderDetails
GROUP BY dbo.CompanyAdd.companyName,
       dbo.OrderDetails.department, 
       dbo.OrderDetails.productSold
HAVING (dbo.User2Company.UsrID = 10)
ORDER BY
       dbo.OrderDetails.department;

This is my code to check if previous record is same as current.

<% dim productSold  
   Do While  Not registerRS.EOF 
     Response.write"<table>" 
     Response.write "<tr>" %> 
     <td height="265.6" width="264.56">
       <%=rS.Fields("companyName")%> <br />
       <%=rS.Fields("department") %><br />
       <%=rS.Fields("productSold") %><br /> 
     </td>      
     <%
      Response.write "</tr>" 
      If r = 4 then r = 1 
      'Next  
        if rS.Fields("productSold") <> productSold then
          Response.write("productSold:" & productSold) 
        end if
      rS.movenext 
   loop  
   Response.write "</table> "  
   rS.close 
   set rS=nothing   
%>

you are getting 1 dept with 2 records, it's just putting the dept on both records, correct? you really can't easily get rid of that in your result set, I would recommend looping through your results and only show the dept for the first record and then the products sold for the 1st and 2nd records. — Robert, Jan 03 '12 at 04:14
So I will have to use nested loop? One to loop until product sold is finished and another till department is done? — JohnDoe4136, Jan 03 '12 at 06:49
you could do that or just use one loop and check that the department has changed and if it has write it, if not don't write it. — Robert, Jan 03 '12 at 11:43
I have used if rS.Fields("productSold") <> product then Response.write product to compare the current with previous record. But the product is blank. Am I missing something? — JohnDoe4136, Jan 04 '12 at 01:46

Robert · Accepted Answer · 2012-01-09T16:42:35.873

Your not setting the variable productSold to rS.Fields("productSold"). Your also missing an end if before the loop statement.

Try modifying your code to be something like this.

<% dim productSold  
   Do While  Not registerRS.EOF 
     Response.write"<table>" 
     Response.write "<tr>" %> 
     <td height="265.6" width="264.56">
       <%=rS.Fields("companyName")%> <br />
       <%=rS.Fields("department") %><br />
       if rS.Fields("productSold") <> productSold then
         Response.write("productSold:" & productSold) 
       end if           
     </td>      
     <%
      Response.write "</tr>" 
      If r = 4 then r = 1 
      'Next  
      if rS.Fields("productSold") <> productSold then
        Response.write("productSold 2:" & productSold) 
      end if
      productSold = rS.Fields("productSold") '<-- Set productSold variable
      rS.movenext 
   loop  
   Response.write "</table> "  
   rS.close 
   set rS=nothing   
%>

I added it outside the if statement but depending on your logic you may want to set it inside the if statement. You commented that you moved the rS.movenext before the loop, it has to be before the loop otherwise your recordset would never get to the next record.

Hi, i tried your code but repeating all the records even when the department remain the same. — JohnDoe4136, Jan 09 '12 at 08:51
I updated the script slightly, my end if wasn't correct for you. I also noticed that your writing out the productSold variable in 2 locations. Are you sure it's writing it the 2nd time when they are the same? I modified the code to check in both places and added a 2 to the 2nd write statement. — Robert, Jan 09 '12 at 16:43

score 0 · Answer 2 · edited May 23 '17 at 11:55

If you are on SQL Server 2005+, you could concatenate all same-department products into single product lists, delimiting them e.g. with line separators (depending on whether you prefer varchar or nvarchar in this case, it could be either CHAR(13) + CHAR(10) or NCHAR(13) + NCHAR(10)).

This site contains plenty of examples of how to concatenate a column of values into a single value. Here's just one of the lot:

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

You can explore the question's Linked section to find more similar questions and, consequently, more examples.

To display select statement - sql query

2 Answers2