Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query - Combine 2 or more lines together

Posted on 2010-08-24
5
Medium Priority
?
252 Views
Last Modified: 2012-05-10
Hello experts,

I created a query to pull items off a sql server 2005 database.  It is pulling what I need, but is it possible to group 2 or more line items together?  The only issue with grouping is that 2 record would be the same except for a field.  I want to combine those 2 items as if it's one.

Example:
ID         Description              Code            Date Due
1234    This is a test.            ABC              8/25/2010
1234     This is a test.           EFT               8/25/2010

Both of the above combined to look like:

ID         Description              Code                     Date Due
1234    This is a test.            ABC, EFT              8/25/2010

Is it possible?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Expert Comment

by:GO-87
ID: 33512266
What you're trying to do, is to apply an AGGREGATE function to the Code column across a bunch of rows, GROUP-ing by the remainder of the fields.

Unfortunately, you cannot use SUM since it only works on numeric values. Instead, you need a sort of AGGREGATE CONCATENATION function.

You can find some examples here:
http://p2p.wrox.com/sql-language/1661-aggregate-concatenation.html 
0
 
LVL 41

Accepted Solution

by:
ralmada earned 1200 total points
ID: 33512401
check the below:
select 	distinct
	t1.ID, 
	t1.Description,
	stuff((select ',' + Code from yourtable where ID = t1.ID for xml path('')), 1,1,'') as Codes,
	t1.[Date Due]
from yourtable as t1

Open in new window

0
 
LVL 4

Assisted Solution

by:GO-87
GO-87 earned 400 total points
ID: 33512485
Or using a function,


select id, Description, dbo.GetCodes(id) AS Code, DateDue
from myTable
group by id, Description, DateDue
 

CREATE FUNCTION GetCodes (@argID AS int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @retCodes varchar(1000)
SELECT @retCodes=''
SELECT @retCodes = @retCodes + Code + ','
  FROM myTable
 WHERE id = @argID
RETURN (@retCodes)
END
GO

Open in new window

0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 400 total points
ID: 33512501
Try this


CREATE TABLE #Info( 
Id int,
Name VARCHAR(1000),
TName VARCHAR(1000),
[Date] DateTime
)

INSERT INTO #Info (Id,Name,TName,Date)
VALUES (1234,'This is a test','ABC','8/25/2010')

INSERT INTO #Info (Id,Name,TName,Date)
VALUES (1234,'This is a test','EFT','8/25/2010')

SELECT  Id,name ,
       LEFT(column_name,LEN(column_name) - 1)   AS column_name ,[Date]
FROM   (SELECT Id,name ,[Date], 
               (SELECT TName + ',' AS [text()] 
                FROM   #info AS internal 
                WHERE  internal.name = t_name.name 
                FOR xml PATH ('') 
               ) AS column_name 
        FROM   (SELECT   Id,name ,[Date]
                FROM     #Info
                GROUP BY name,Id,[Date]) AS t_name) AS trimmed;

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33518729
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question