SQL Server 2012 :: Merge Multiple Rows From Single Table
Nov 17, 2014
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
View 4 Replies
ADVERTISEMENT
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Feb 19, 2015
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
View 1 Replies
View Related
Nov 26, 2014
basically i have data like this
order_key comment
1 A
1 B
1 C
2 B
2 D
the data intends to be like this
order_key comment
1 A,B,C
2 B,D
View 3 Replies
View Related
Dec 24, 2014
I have a table that looks like this ...
idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222
I want to merge the data to look like this ...
idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)
-- Insert the records for type 1
INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts
View 2 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
May 13, 2008
Hi,
I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3
NewsDetails
Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2
Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW
Thank you.
View 2 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[Code] ....
View 7 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[code]....
View 2 Replies
View Related
May 13, 2015
I have a set of data which contains individual logon and logoff data. The table is as follows:
AGENTID, EVENTTIME, CURRENTSTATE
1234, 2015-05-12, 15 (For Logon) or 25 (For Logoff)
I'm hoping to extract this data as follows:
AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION
View 19 Replies
View Related
Nov 27, 2014
The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
CREATE TABLE #AWP (
[TransDate] [datetime] NULL,
[Description] [varchar](1000) NULL,
[Amount] [float] NULL,
[TotalDueNow] [float] NULL,
[code]....
View 4 Replies
View Related
Sep 18, 2014
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london
where paris
where toronto
why us
why japan
why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as (
select cq.ConditionalQuestionID from ConditionalQuestions cq
inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y')
select distinct q.Question, a.Answer from QuestionAnswers qa
inner join Answers a on a.AnswerID = qa.AnswerID
inner join Questions q on q.QuestionID = qa.QuestionID
inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
View 4 Replies
View Related
Feb 12, 2015
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
View 1 Replies
View Related
Dec 15, 2007
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2
View 1 Replies
View Related
Aug 1, 2014
I have a table named LEDGER
LEDGER has two columns named PATID and CODE
Example data:
PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092
I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.
View 5 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Jul 20, 2005
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
View 3 Replies
View Related
Sep 20, 2007
Hi,
I have 3 tables with the follwing schema
Table <Category>
{
UniqueID,
LastDate DateTime
}
Assume the follwing tables with data following the above schema
Table Cat1
{
1, D1
2, D2
3, D3
}
Table Cat2
{
2, D4
3,D5
4, D6
}
Table Cat3
{
1, D7
3,D8
5,D9
}
I have a Master and the schema is as follows
Table master
{
UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{
UniqueId cat1 cat2 Cat3
------------ --------- ------- -----------
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}
Please remember the column names will be same as that of table names
can any one pelase let me know the query t o acheive this
Thanks for your quick response
~Mohan Babu
View 8 Replies
View Related
Oct 25, 2007
I am using SQL 2000
I am getting a syntax error when I parse this sql script:
insert into Elec_Sub_Test1
values ('10-20-2007',35),
('10-21-2007',24)
What is the correct syntax to insert mutlipe rows in a single table.
View 4 Replies
View Related
Jul 16, 2015
We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.
Below is the Table Structure.
Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
View 6 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
May 24, 2015
How to combine multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID
The reult for the above query is:
RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1
I need the result like the below
RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46
View 2 Replies
View Related
Aug 12, 2015
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
View 3 Replies
View Related
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Oct 28, 2015
I have the following results:
CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110
And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.
View 9 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Jan 30, 2015
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
View 1 Replies
View Related
Jan 27, 2014
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.
People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801
[code]....
Results
PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom
View 8 Replies
View Related
Mar 3, 2015
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
For Eg:
Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L
View 9 Replies
View Related
May 7, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
ID Pat_ID
1 A
2 A
3 A
4 A
5 A
6 B
7 B
8 B
9 C
10 D
11 D
I want output for the above as:
Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null
Please help me. Thanks!
View 6 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 18, 2015
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....
View 7 Replies
View Related
