Duplicate records to show in one line

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

General SQL Server Forums

New to SQL Server Programming

Duplicate records to show in one line

Author

Topic

spitfire122
Starting Member

11 Posts

Posted - 2011-06-22 : 14:42:24

I have a query where a left join brings duplicate records. However, i need all these records (because they're multiple records) - the wrk.workout_type_name in one single row. For example, wrk.workout_type_name to return like Sale, modification, release to third party

how do i write this?


select
x.*

from
(
select
	sv.loan_number
	,sv.delinquency_status_02
	,ROW_NUMBER() OVER(PARTITION BY pc.loan_number ORDER BY ls.create_date DESC) AS seq_id
	,rid.reason_for_default_name
	,wrk.workout_type_name as options_discussed
from
	loan.vw_servicing_t sv

	left join valuation.vw_last_value_t val with(nolock)
	on sv.loan_number = val.loan_number
)x
where
x.seq_id = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 14:57:20

This blog has a few different ways of doing what you are describing - take a look to see if that helps you. If you run into difficulties, please post some sample data and I am sure many people on this forum would be able to offer useful suggestions: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-23 : 00:06:14

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

>> I have a query where a left outer join brings duplicate records [sic] . <<

Rows are not records.

>> However, I need all these records [sic] (because they're multiple records [sic]) - the WRK.workout_type in one single row. For example, WRK.workout_type to return like Sale, modification, release to third party <<

You do display formatting in the front end and never in the data base. That is one of the MANY ways that rows are nothing like records! Normal forms? Scalar values? Remember any of this stuff? Fields have none of these requirements and you can find some stinking kludges in SQL to make RDBMS back into 1950's COBOL. Not a good idea.

A data element can be a type or a name or an identifier, or any numer of what ISO calls an “attribute property”, but they cannot be that hybrid garbage you posted. That “workout_type_name” is bad, but at least it has a base attribute; “seq_id” is really awful! No base attribute in the data model/ And VIEWs are named just like tables, that silly VW_ thing says you are working with Volkswagen or have no idea about SQL and data modeling.

Here is a quick clean up on your code.

SELECT X.*
FROM (SELECT SV.loan_number, SV.delinquency_02_status,
ROW_NUMBER()
OVER(PARTITION BY PC.loan_number
ORDER BY LS.creation_date DESC) AS delinquency_seq,
RID.loan_default_name,
WRK.workout_type
FROM Loan.Servicing AS SV
LEFT OUTER JOIN
Valuation.Last_Loan_Values AS VAL
ON SV.loan_number = VAL.loan_number)
AS X
WHERE X.delinquency_seq = 1;

Drop all the stuff related to X; pass the set of multiple rows and let the application format it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources