[SOLVED] Consolidate similar records ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

[SOLVED] Consolidate similar records ???

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-19 : 15:35:17

I have table 'name' that looks like:

Name | Note
Kevin | 123
Kevin | 456
Jerry | 123
Moe | 764
Kevin | 789
Moe | 111

and I'd like to create a table/view that will create the output:
Name | Note
Kevin | 123, 456, 789
Jerry | 123
Moe | 764, 111

How can I consolidate similar records like that?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-19 : 15:46:51

One way would be to use the XML PATH method like this:

select distinct
	a.name,
	b.notes
from
	YourTable a
	outer apply
	(
		select stuff
		((
			select 
				',' as [text()],
				note as [text()]
			from
				YourTable b
			where
				b.name = a.name
			for xml path('')
		),1,1,'')
	) b(notes);

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-19 : 16:28:21

I have a table:

Serial | Note | NoteType
1 | 123 | A
1 | 456 | A
1 | Old | B
1 | Blah | B

I want to get the output:
Serial |A Note | B Note
1 |123, 456| Old, Blah

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 10:03:04

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 10:19:38

Does the previous query that I posted work for you? If it does, you should be able to pivot that - like shown below, although I have not been able to test the code.

SELECT * FROM 
(
select distinct
	a.name,
	a.NoteType,
	b.notes
from
	YourTable a
	outer apply
	(
		select stuff
		((
			select 
				',' as [text()],
				note as [text()]
			from
				YourTable b
			where
				b.name = a.name
				AND b.NoteType = a.NoteType
			for xml path('')
		),1,1,'')
	) b(notes)
) s
PIVOT 
(MAX(Notes) FOR NoteType IN ([A],[B]))P;

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 10:29:14

Thanks for your reply but both A and B are populated w/ null values and not the text I am looking for.

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 10:35:52

I had this statement working yesterday but it is throwing an error now:


select serial_num, concat_note_txt as hwn_note,
ind_note = 
  (
   select concat_note_txt as ind_note
   from uapm_notes T1
   where T1.serial_num = uapm_notes.serial_num and note_indicator like 'ind'
   )
from uapm_notes
where note_indicator like 'hwn'

Now it is erroring out w/ "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 10:41:43

quote:
Originally posted by darms21

I had this statement working yesterday but it is throwing an error now:

select serial_num, concat_note_txt as hwn_note,
ind_note =
(
select concat_note_txt as ind_note
from uapm_notes T1
where T1.serial_num = uapm_notes.serial_num and note_indicator like 'ind'
)
from uapm_notes
where note_indicator like 'hwn'

Now it is erroring out w/ "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

This query is very different from the sample data that you had posted. The query I posted is for the sample data.

If your subquery returns more than one row for any given row in the outer query this error will happen. If there is more than one row for a given serial_num with note_indicator = 'ind' that will happen.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 10:48:57

I tested the query I posted earlier this morning - see sample data and query below. You should be able to copy the code, run it and see what it does. From what I see, it's output is exactly what I understood to be what you were asking for.

If you post DDL for your table and sample data in a form that someone can copy and paste to SSMS, many people on the forum would be able to provide more accurate and timely answers. Many people (including me) shy away from responding to questions that do not have DDL and sample data because it takes time and efforts to create the DDL and sample data. And, it is hard to write the code without some sample data that can be used to test.

-----------------------------------------------------------------------------------
-- DDL FOR TABLE
CREATE TABLE #YourTable (NAME VARCHAR(32), Note VARCHAR(32), NoteType VARCHAR(32));
-- SAMPLE DATA
insert into #YourTable values ('1','123','A')
insert into #YourTable values ('1','456','A')
insert into #YourTable values ('1','Old','B')
insert into #YourTable values ('1','Blah','B')
-----------------------------------------------------------------------------------

-- QUERY
SELECT * FROM 
(
select distinct
	a.name,
	a.NoteType,
	b.notes
from
	#YourTable a
	outer apply
	(
		select stuff
		((
			select 
				',' as [text()],
				note as [text()]
			from
				#YourTable b
			where
				b.name = a.name
				AND b.NoteType = a.NoteType
			for xml path('')
		),1,1,'')
	) b(notes)
) s
PIVOT 
(MAX(Notes) FOR NoteType IN ([A],[B]))P;


-----------------------------------------------------------------------------------
-- CLEANUP
DROP TABLE #YourTable;

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 12:11:23

Let me be more accurate, i appoligize.
My table is:

USE [dsm_dw]
GO
/****** Object: Table [dbo].[uapm_notes] Script Date: 04/20/2012 12:10:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[uapm_notes](
[uuid] [binary](16) NULL,
[serial_num] [nvarchar](64) NULL,
[Note_TXT] [nvarchar](4000) NULL,
[Note_Indicator] [nvarchar](3) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

and this is not working....

SELECT * FROM

(
select distinct
	a.serial_num,
	a.note_indicator,
	b.notes
from
	uapm_notes a
	outer apply
	(
		select stuff
		((
			select 
				',' as [text()],
				note_Txt as [text()]
			from
				uapm_notes b
			where
				b.serial_num = a.serial_num
				AND b.note_indicator = a.note_indicator
			for xml path('')
		),1,1,'')
	) b(notes)
) s
PIVOT 
(MAX(Notes) FOR note_indicator IN ([A],[B]))P;

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 12:19:50

NULL is returned for both a and b as I said.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 12:52:17

Instead of A and B, you should use the values of the note_indicator in your table. You can find the note_indicators by running this query:

SELECT DISTINCT note_indicator FROM [dbo].[uapm_notes]

Then, replace the A and B in the query below with those values - see in red - I am assuming your note indicators are ind and hwn

If you have more than 2 note indicators, you can add additional indicators to that list.

If you don't know what the note indicators would be, then there is more work to do.

If you will have an unknown number of note indicators, then you will need to use a dynamic pivot query.

SELECT * FROM 
(
select distinct
	a.serial_num,
	a.note_indicator,
	b.notes
from
	uapm_notes a
	outer apply
	(
		select stuff
		((
			select 
				',' as [text()],
				note_Txt as [text()]
			from
				uapm_notes b
			where
				b.serial_num = a.serial_num
				AND b.note_indicator = a.note_indicator
			for xml path('')
		),1,1,'')
	) b(notes)
) s
PIVOT 
(MAX(Notes) FOR note_indicator IN ([ind],[hwn]))P;

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 12:59:29

Thank you for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources