Solved

SQL Query - Combine 2 or more lines together

Posted on 2010-08-24
5
247 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 300 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 100 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 100 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax error creating JSON recordset 4 43
SQL query 7 50
Please explain Equi-join 3 44
Please explain the difference between EXCLUDE, INTERSECT and JOIN 7 52
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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