| Author |
 Topic  |
|
|
darms21
Yak Posting Veteran
53 Posts |
 Posted - 04/19/2012 : 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? |
Edited by - darms21 on 04/20/2012 12:59:14
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/19/2012 : 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
53 Posts |
Posted - 04/19/2012 : 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 |
Edited by - darms21 on 04/19/2012 17:12:04 |
|
|
|
darms21
Yak Posting Veteran
53 Posts |
Posted - 04/20/2012 : 10:03:04
|
| Any ideas? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 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; |
Edited by - sunitabeck on 04/20/2012 10:20:06 |
|
|
|
darms21
Yak Posting Veteran
53 Posts |
Posted - 04/20/2012 : 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
53 Posts |
Posted - 04/20/2012 : 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." |
Edited by - darms21 on 04/20/2012 10:36:41 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 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
53 Posts |
Posted - 04/20/2012 : 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;
|
Edited by - darms21 on 04/20/2012 12:12:47 |
|
|
|
darms21
Yak Posting Veteran
53 Posts |
Posted - 04/20/2012 : 12:19:50
|
| NULL is returned for both a and b as I said. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 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
53 Posts |
Posted - 04/20/2012 : 12:59:29
|
| Thank you for the help! |
|
|
| |
Topic |
|
|
|
[SOLVED] Consolidate similar records ???
