T-SQL query combine rows into a single column
-
Hi all,
I'm a newbie to T-SQL. Please can you help me with the below query . Thanks in advance!
my desired o/p
------------------
my input code
------------------
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test;
create table test (person_id int , name_change nvarchar(75) )
go
insert into test values (1,'Mr Herby Spike')
insert into test values (1,'Mr Herby Spoke')
insert into test values (2,'miss Anne had')
insert into test values (2,'miss Anne hadbreakfast')
insert into test values (2,'miss Anne hadlunch')
insert into test values (3,'miss Laurrel hadlunch')
insert into test values (3,'miss Laurrel hadbreakfast')
insert into test values (3,'miss Laurrel hadtea')
insert into test values (3,'miss Laurrel haddinner')
go
select * from dbo.test;
go -
I think this does what you want:
select person_id,
string_agg(name_change,' || ')
from dbo.test
group by person_id; -
If this is a 'from/to' scenario, where ordering is important, you need to add one or more columns to the source data to allow the results to appear in the correct order. EffectiveDate would be a possibility, as would VersionNumber, otherwise the order of the results is not guaranteed, from one execution to the next.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
just one more thing .. if I need a count column as well, how can I get his in a single query..
Regards,
D
-
Here is a version which includes ordering and your requirement for a count:
DROP TABLE IF EXISTS #Test;
CREATE TABLE #Test
(
person_id INT NOT NULL
,Version INT NOT NULL
,name_change NVARCHAR(75) NOT NULL
,
PRIMARY KEY CLUSTERED (
person_id
,Version
)
);
INSERT #Test
(
person_id
,Version
,name_change
)
VALUES
(1, 1, 'Mr Herby Spike')
,(1, 2, 'Mr Herby Spoke')
,(2, 1, 'miss Anne had')
,(2, 2, 'miss Anne hadbreakfast')
,(2, 3, 'miss Anne hadlunch')
,(3, 1, 'miss Laurrel hadlunch')
,(3, 2, 'miss Laurrel hadbreakfast')
,(3, 3, 'miss Laurrel hadtea')
,(3, 4, 'miss Laurrel haddinner');
SELECT t.person_id
,NameChange = STRING_AGG(t.name_change, ',') WITHIN GROUP(ORDER BY t.Version)
,ct = COUNT(1)
FROM #Test t
GROUP BY t.person_id;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
>> I'm a newbie to T-SQL. <<
Please read any book on basic RDBMS. As Dykstra used to say to his students, "you're doing everything completely wrong."
CREATE TABLE Tests
(person_id INTEGER NOT NULL, ---wrong data type
name_change NVARCHAR (75), --- what does Null mean?
PRIMARY KEY (??));
By definition, not as some vague option, a table must have a key. But you published what are basically a bunch of punchcards with redundant data and no possibility of having a unique row. You really don't know what a key is! Even before we got to RDBMS, the goal of databases was to remove redundancy. Not increase it.
So not only are you doing it wrong, you don't know the syntax of the insert statement has a row constructor. Instead you're inserting one row at a time. Just the way we used to insert punchcards into a magnetic tape file in the 1960s. Here is direct translation of your punchcards into unusable poor SQL.
INSERT INTO Tests -- garbage code!
VALUES
(1,'Mr Herby Spike'),
(1,'Mr Herby Spoke').
(2,'miss Anne had'),
(2,'miss Anne hadbreakfast'),
(2,'miss Anne hadlunch'),
(3,'miss Laurrel hadlunch'),
(3,'miss Laurrel hadbreakfast'),
(3,'miss Laurrel hadtea'),
(3,'miss Laurrel haddinner');
An identifier is usually shown by the postfix "_id" and it is by definition on a nominal scale. This means you can't use numerics for it because you don't do any calculations. But ignoring the DDL, there is a more fundamental problem. If you read Dr. Codd any book on RDBMS, you will see that columns are supposed to be scalar values, not concatenated strings. In SQL and other tiered architectures. Such display formatting is done in a presentation layer, and never in the database.
What you've got here, is not really help, but a bunch of proprietary kludges. Please stop what you're doing and catch up on your reading.
Please post DDL and follow ANSI/ISO standards when asking for help.
- RDBoy wrote:
Hi all,
I'm a newbie to T-SQL. Please can you help me with the below query . Thanks in advance!
my desired o/p
------------------
my input code
------------------
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test;
create table test (person_id int , name_change nvarchar(75) )
go
insert into test values (1,'Mr Herby Spike')
insert into test values (1,'Mr Herby Spoke')
insert into test values (2,'miss Anne had')
insert into test values (2,'miss Anne hadbreakfast')
insert into test values (2,'miss Anne hadlunch')
insert into test values (3,'miss Laurrel hadlunch')
insert into test values (3,'miss Laurrel hadbreakfast')
insert into test values (3,'miss Laurrel hadtea')
insert into test values (3,'miss Laurrel haddinner')
go
select * from dbo.test;
goUnless you have an extra column like Phil added to his code, your original code has no guarantee at to what the order of the names in the output will be. It that important at all?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - jcelko212 32090 wrote:
This means you can't use numerics for it because you don't do any calculations.
Instead of going through it all yet again, I'll summarize just by saying that's total rubbish.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply