concatenating rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

concatenating rows

Author

Topic

bobbarmack
Starting Member

2 Posts

Posted - 2011-04-19 : 14:40:23

I have a query which returns 4 columns:

ID DATA_NAME RAW_DATA TIMESTAMP

I want to say where column 1,2,4 are the same for rows 1 and 2 or 1,2, and 3, then combine/concatenate data from row 1 and row 2(like data will always be sequential) into one single row.

ID, DATA_NAME, RAW_DATA are varchar and TIMESTAMP is date/time

Is there a way to do this if I only have read-only access and lack the ability to write to a temporary table?

I'm looking at nested queries but I'm having trouble wrapping my brain around this issue.

Normally I would post-process via excel or access macro, but this query runs on a unix box every 5 minutes via chron job and then e-mails recipients if data exists. There's probably a way to concatenate the results in Unix but that's out of my technical league. I'm just trying to tell the guys who requested this why or why it cannot be done. Actually it will probably be a query which lasts more than 5 minutes if we add nested queries to it, but we could conceivably run it every 15 or 30 mins.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 14:44:04

Can you post some sample data and expected results? That would make it much easier for someone to understand the problem and respond. I have a vague understanding of what you are trying to do, but not enough to offer any useful suggestions.

bobbarmack
Starting Member

2 Posts

Posted - 2011-04-19 : 15:16:05

copying and pasting is no good, what is this forum's process for posting sample data as attachments?

K-LO thanks you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 15:39:05

I don't know of a URL where you can post pictures with links to here, but you may not need to do all that.

Here is a link that will give you some info on how to post:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Or you could read my less than perfect explanation below (and this is me pretending to be you talking):



Sample data in my table is this:

ID    DATA_NAME     RAW_DATA    TIMESTAMP
1      Cost           57.5       20110401
2      Cost           10.0       20110401
3      Payroll        33.7       20110401
4      Taxes          27.5       20110401

I want to get the output as:
1,2    Cost           67.5       20110401
3      Payroll        33.7       20110401
4      Taxes          27.5       20110401

Here, the first and second rows were combined because DATA_NAME and TIMESTAMP are the same. Column 1 is comma-separated and column 3 is the sum.

(-- END OF ME PRETENDING TO BE YOU --)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-19 : 16:38:50

is email an attachment or just pasted in body of email? is chron job hitting a database such as mysql?

If you don't have the passion to help people, you have no passion

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-04-21 : 09:45:38

i create a temp table to hold all the values i want
i then create a query that runs and inserts the base values to a 2nd temp table
i then run an update query to insert the values into the single row into the final temp table see below- its not the best format when pasted in here. you can see that i am inserting the last 4 columns into the single row. hope this helps

CREATE PROCEDURE [dbo].[xxxxxxxxxxxxxxxxxxxxx]
( @StartDate datetime,
@EndDate datetime)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@start_date datetime,
@end_date datetime

--use convert to mm/dd/yyyy with default Start_time 12:00:00 and default End_time 11:59:59
SELECT @start_date = convert(datetime,convert(varchar(10), @StartDate, 101))
SELECT @end_date = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @EndDate, 101))))

CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
CLIENT_GUID numeric(16,0),
ClientVisitGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
LOS_day INT,
AUTHORED_BY varchar(50),
AUTHOR_DISCIPLINE VARCHAR(255),
ATTENDING_NOTE_NAME varchar(255),
DISCHARGE_LOC varchar(255),
DIAGNOSIS varchar(2000), TYPECODE varchar(255),
PROVIDER_NAME VARCHAR(255), PROVIDER_DISCIPLINE varchar(255),
hcd_status varchar(30),
ADMISSION_DIAGNOSIS varchar(2000),
PRINCIPAL_DIAGNOSIS VARCHAR(2000),
SECONDARY_DIAGNOSIS VARCHAR(2000),
WORKING_DIAGNOSIS VARCHAR(2000))

--Get all patients who had a Physician Progress Notes Note within the select date time

SELECT DISTINCT
'WEST' AS Site,
CASE WHEN loc.ParentGUID = 2000001000061001 THEN 'ALLEN' END AS Campus,
cv.ClientGUID AS CLIENT_GUID,
cv.GUID AS ClientVisitGUID,
REPLACE(cv.IDCode, ' ', '') AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
cv.AdmitDtm AS ADMIT_DATE,
cv.DischargeDtm AS DISCHARGE_DATE,
cd.AuthoredDtm AS AUTHOR_DATE,
DATEDIFF(dd, cv.AdmitDtm,cv.DischargeDtm) AS LOS_day,
dbo.CV3User.DisplayName AS Authored_by,
dbo.CV3User.OrderRoleType AS AUTHOR_DISCIPLINE,
cd.DocumentName AS ATTENDING_NOTE_NAME,
cv.DischargeDisposition AS DISCHARGE_LOC,
dbo.CV3HealthIssueDeclaration.ShortName AS DIAGNOSIS,
dbo.CV3HealthIssueDeclaration.TypeCode,
dbo.CV3HealthIssueDeclaration.status as hcd_status,
x.PROVIDER_NAME,
x.PROVIDER_DISCIPLINE
into #init
FROM
dbo.CV3ClientVisit AS cv WITH (nolock)
INNER JOIN
dbo.CV3Location AS loc WITH (nolock) ON cv.CurrentLocationGUID = loc.GUID AND cv.TypeCode = 'Inpatient'
AND loc.ParentGUID ='2000001000061001'
INNER JOIN
dbo.CV3ClientDocument AS cd WITH (nolock) ON cv.GUID = cd.ClientVisitGUID and cv.serviceguid='8000001011111001'
INNER JOIN
dbo.CV3PatientCareDocument WITH (nolock) ON cd.PatCareDocGUID = dbo.CV3PatientCareDocument.GUID
INNER JOIN
dbo.CV3DocumentReviewCategory WITH (nolock) ON
dbo.CV3PatientCareDocument.DocReviewCategoryGUID = dbo.CV3DocumentReviewCategory.GUID
and MasterCategoryName = 'Physician Progress Notes'
INNER JOIN
dbo.CV3User WITH (nolock) ON cd.AuthoredProviderGUID = dbo.CV3User.GUID
INNER JOIN
dbo.CV3HealthIssueDeclaration WITH (nolock) ON cv.GUID = dbo.CV3HealthIssueDeclaration.ClientVisitGUID AND
cv.ClientGUID = dbo.CV3HealthIssueDeclaration.ClientGUID
AND dbo.CV3HealthIssueDeclaration.TypeCode IN ('Admitting Dx', 'Secondary Dx','Working Dx', 'Principal Dx')
INNER JOIN
(SELECT
dbo.CV3CareProviderVisitRole.ClientVisitGUID AS GUID,
dbo.CV3CareProvider.DisplayName AS PROVIDER_NAME,
dbo.CV3CareProviderVisitRole.RoleCode AS PROVIDER_DISCIPLINE
FROM
dbo.CV3CareProvider WITH (nolock)
INNER JOIN
dbo.CV3CareProviderVisitRole WITH (nolock) ON dbo.CV3CareProvider.GUID = dbo.CV3CareProviderVisitRole.ProviderGUID AND
CV3CareProviderVisitRole.RoleCode IN ('Attending', 'Resident')) AS x
ON x.GUID = cv.GUID
WHERE (cv.AdmitDtm BETWEEN @start_date AND @end_date)

ORDER BY cv.VisitIDCode,cd.DocumentName

--Add all data to the final table
INSERT #Final
(
Site,
Campus,
CLIENT_GUID,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
LOS_day,
AUTHORED_BY,
AUTHOR_DISCIPLINE,
ATTENDING_NOTE_NAME,
DISCHARGE_LOC,
DIAGNOSIS,
TYPECODE,
PROVIDER_NAME,
PROVIDER_DISCIPLINE
)
SELECT distinct
Site,
Campus,
CLIENT_GUID,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
LOS_day,
AUTHORED_BY,
AUTHOR_DISCIPLINE,
ATTENDING_NOTE_NAME,
DISCHARGE_LOC,
DIAGNOSIS,
TYPECODE,
PROVIDER_NAME,
PROVIDER_DISCIPLINE
FROM #INIT

-- Update final table to assign Date and Time for DIAGNOSIS for each patient
UPDATE #Final
SET ADMISSION_DIAGNOSIS = I.DIAGNOSIS
FROM #fINAL F, #INIT I
WHERE I.CLIENT_GUID = F.CLIENT_GUID AND
I.ClientVisitGUID = F.ClientVisitGUID and I.hcd_status='Active' AND I.TypeCode ='Admitting Dx'

UPDATE #Final
SET PRINCIPAL_DIAGNOSIS = I.DIAGNOSIS
FROM #fINAL F, #INIT I
WHERE I.CLIENT_GUID = F.CLIENT_GUID AND
I.ClientVisitGUID = F.ClientVisitGUID and I.hcd_status='Active' AND I.TypeCode ='Principal Dx'

UPDATE #Final
SET SECONDARY_DIAGNOSIS= I.DIAGNOSIS
FROM #fINAL F, #INIT I
WHERE I.CLIENT_GUID = F.CLIENT_GUID AND
I.ClientVisitGUID = F.ClientVisitGUID and I.hcd_status='Active' AND I.TypeCode ='Secondary Dx'

UPDATE #Final
SET WORKING_DIAGNOSIS = I.DIAGNOSIS
FROM #fINAL F, #INIT I
WHERE I.CLIENT_GUID = F.CLIENT_GUID AND
I.ClientVisitGUID = F.ClientVisitGUID and I.hcd_status='Active' AND I.TypeCode ='Working Dx'

--Final select

select distinct
Site,
Campus,
CLIENT_GUID,
ClientVisitGUID,
MRN,
EncNumber,
PATIENT,
ADMIT_DATE,
DISCHARGE_DATE,
LOS_day,
AUTHORED_BY,
AUTHOR_DISCIPLINE,
ATTENDING_NOTE_NAME,
DISCHARGE_LOC,
PROVIDER_NAME,
PROVIDER_DISCIPLINE,
ADMISSION_DIAGNOSIS,
PRINCIPAL_DIAGNOSIS,
SECONDARY_DIAGNOSIS,
WORKING_DIAGNOSIS
from #FINAL
order by mrn

DROP TABLE #INIT
DROP TABLE #Final

END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources