Solved

SQL Query - Combine 2 or more lines together

Posted on 2010-08-24
5
241 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
5 Comments
 
LVL 4

Expert Comment

by:GO-87
Comment Utility
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 300 total points
Comment Utility
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 100 total points
Comment Utility
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 100 total points
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now