|
Column1 Column2
-------- ----------
Vaugh William
Vaugh Smith
Woods Jane
Expected Output :
Vaugh, William-Smith/Woods,Jane
Condition : There could be n number of rows in the sample table, there could be n number of woods too.
display all the common surname having different names meaning repeated Last names will be once then all the first names associated with it.
truncated at 30 characters max.
How can I get this output in SQL query ?
|
|
|
|
|
This is not clear.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
What extra information will make it clear ?
|
|
|
|
|
It looks like you want a single record as your output? What are the conditions?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
yes there is just single cell output.
but conditions are that cell can not have more than 30 characters.
Even there are 10 or 15 rows in a table, the SQL query should give single cell output.
|
|
|
|
|
That doesn't make any sense. You want to concatenate everything into a single value? If so you can do similar to:
DECLARE @temp NVARCHAR(MAX)
SELECT @temp = COALESCE(@temp + ', ', '') + field1
FROM table
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I want to display the unique last names followed by their respective first names separated by a '-' and, then '/' , then again the next last name(If it is not distinct , then add their first names separated by '-')followed by its respective firstname and so on..
|
|
|
|
|
How do you know which first name belongs to which last name?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
This sort of manipulation is best done in the UI, not the database.
However, using the techniques from this blog post[^] leads to the following (rather ugly) solution:
SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM YourTable As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM YourTable As T1
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am getting the combined names for the whole table. How to restrict it for a single id? Can you please tell where should I put the condition in the code you have shown above ?
|
|
|
|
|
What do you mean by "a single id"? There's no mention of any column called "id" in your question, or any of the other messages in this thread.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
ID Column1 Column2
-- -------- ----------
7 Vaugh William
7 Vaugh Smith
6 Woods Jane
6 Woods Joseph
6 Wright Adam
6 Wright John
Suppose I have to find the combined name only for id=6 , then where should I put the condition in the code?
The expected output :
Woods,Jane-Joseph/Wright,Adam-John
|
|
|
|
|
So the ID is the same for each surname?
In that case, you just need to add a WHERE clause between the FROM YourTable As T1 and GROUP BY Surname lines:
SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM YourTable As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM YourTable As T1
WHERE T1.ID = @TheIDToFind
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
ID Column1 Column2
-- -------- ----------
7 Vaugh William
7 Vaugh Smith
6 Woods Jane
6 Woods Joseph
6 Wright Adam
6 Wright John
Thanks, I exactly put the condition at the place you suggeted, but still the output I get is as below
WRONG OP : Vaugh,William-Smith/Woods,Jane-Joseph/Wright, Adam-John
Expected OP : Woods,Jane-Joseph/Wright, Adam-John
|
|
|
|
|
I've just tried the code here on your sample data, and I get the expected output.
DECLARE @T TABLE
(
ID int NOT NULL,
Surname varchar(10) NOT NULL,
Forename varchar(10) NOT NULL
);
INSERT INTO @T (ID, Surname, Forename)
VALUES
(7, 'Vaugh', 'William'),
(7, 'Vaugh', 'Smith'),
(6, 'Woods', 'Jane'),
(6, 'Woods', 'Joseph'),
(6, 'Wright', 'Adam'),
(6, 'Wright', 'John')
;
SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM @T As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM @T As T1
WHERE T1.ID = 6
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Here's a SQL Fiddle with the same query, which also generates the correct output:
http://sqlfiddle.com/#!3/300af/1[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I had to add few extra condition suitable to my table, but it DID WORK !! 
I have learnt about STUFF and FOR XML PATH. Thanks a ton for all your time Richard.
|
|
|
|
|
If my one of customer saying they could a generate a report withing 30 second before and now they have to wait for 3min so how I can track that as a Senior Technical Support Analyst that whether it is a database issue or programming issue ?
Chaminda Bandara
|
|
|
|
|
Chaminda Aruna Bandara wrote: as a Senior Technical Support Analyst
Use your skills and experience, and run some tests on the system. Has something changed recently, is the database server having problems, is the network slow ... ?
|
|
|
|
|
Check the execution plan and the index fragmentation, to start.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
If there have been no changes to the code base then it is a server issue, easy. You really should know if there have been changes to the code!
There are a bunch of tuning possibilities that you should be aware/doing from degragging thee indexes to tuning the queries to checking disk space and fragmentation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If we assume that the issue with indexes how can I optimize using indexes ?
|
|
|
|
|
I would use the execution plan to identify bottlenecks in the existing indexes and any missing indexes for the query, I would then defrag those indexes
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm working on a system where data integrity is important so rather than just updating records we're always going to insert a new one, quite often there will be links in the system to previous versions so it'd be useful if this data was easily available.
Previously this system was being developed by a guy who's now left the company, he's been using GUIDs as Ids and that's something I'm tryin get get away from.
The current structure we have is:
Guid Id
Guid RootId
int Version
RootId is the base ID and Id is the primary key, there is also an Audit table where adding an entry for the ID can mark it as deleted, this makes the select statements a bit over complicated since you have to find the maximum version for each RootId then join back to get the actual data and then join again to find any audit entries.
I was wondering, what other simpler versioning strategies are there?
|
|
|
|
|
What I have done is build an audit table:
id int
operation nvarchar(50)
table_name nvarchar(200)
col_name nvarchar(200)
key_col nvarchar(200)
key_val nvarchar(200)
col_val_prior nvarchar(MAX)
col_val_new nvarchar(MAX)
username nvarchar(50)
transaction_dt datetime
batch nvarchar(MAX)
id - unique id on this table
operation - insert,delete or update
table_name - name of table operation pertains to
col_name - name of the column operation pertains to
key_col - name of the key column operation pertains to
key_val - value of the key column operation pertains to
col_val_prior - value of col_name column before operation
col_val_new - value of col_name column after operation
username - name of user running operation
transaction_dt - datetime trigger was run
batch - guid that uniquely identifies an operation
I then created three triggers that I add to all tables to be audited(the triggers cover insert, update and delete operations).
This allows me to see all operations on tables I wish to audit.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
