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
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.
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.
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] ....
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]....
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.
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
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
_ _ _ _ _ _ _ _
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.
The recipe preparation instructions are stored in a table by RecipeID. The prep instructions are in a single VARCHAR(MAX) column and look something like this:
1. Boil Water
2. Add noodles
3. Add cheese sauce
4. Stir well
Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row. How would I accomplish this in T-SQL?
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.
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!
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies View RelatedI have a table that has columns like following
trans_code
gl_code
12qw3
a1235
12qw3
a6578
12qw3
t531
e34g6
gt25
e34g6
y7327
e34g6
v23409
Is there an easy way to turn this into format below ??
trans_code
gl_code_1
gl_code_2
12qw3
a1235
a6578
12qw3
a1235
[code]....
I have 2 columns (ID, Msg_text) in a table where i need to combine every 3 rows into single row. What would be the best option i have? I know by using 'STUFF' and 'XML PATH' i can convert all the rows into a single row but here i'm looking for every 3 rows into a single row.
View 3 Replies View RelatedHow to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,
[Code] ....
Is it possible to return the data in the following format merging 2 lines of data into one output:
SiteID ReadStartDate ReadEndDate ReadStartIncome ReadEndIncome
L0020 19/05/2015 05:00 20/05/2015 05:00 85.98 145.98
L0101 19/05/2015 22:07 20/05/2015 22:14 1,936.08 1,438.89
L0102 20/05/2015 21:16 19/05/2015 21:48 143.65 243.5
I am using SQL 2008 R2.
I have a table Customer with below column
CustomerNumber , FName,LName,DOB
I have either 2 or 1 row for each customer number I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.
example:
CustNumber Fanme Lname DOB
1 Sam tiller 08/26/1981
1 Joe timy 01/02/1986
2 jack niks 09/09/1990
Result I want:
CustNumber Fname1 Lname1 DOB1 Fname2 Lname2 DOB2
1 Sam Tiller 08/26/1981 Joe timy 01/02/1986
2 Jack niks 09/09/1990 null null null
Hi All,
We've a table as in the following format:
PK_Column1
PK_Column2
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1
Issue_Date2
Issue_Amount2
Issue_Category2
Issue_Reject2
We need to divide it into two new tables as follows:
UniqueID
PK_Column1
PK_Column2
And
UniqueID
PK_Column1
PK_Column2
Sequence_ID
Issue_Date
Issue_Amount
Issue_Category
Issue_Reject
Unique1
1
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1
Unique2
2
Issue_Date2
Issue_Amount2
Issue_Category2
Unique3
1
xx
xx
Unique4
2
xx
xx
Unique5
3
xx
4
xx
There will be one UniqueID for each row.
We'll get the uniqueID and PK1 and PK2 in a file.
Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table.
Can we do this without using cursors?
This is going to be one time process.
Any ideas are appreciated.
Thanks,
Siva.
Hi,
I've a temp variable where I'm moving some columns like below:
id
value
type1
type2
0
ab
type1val1
type2val1
0
cd
type1val1
type2val1
0
ef
type1val1
type2val1
1
ab
type1val2
type2val2
1
cd
type1val2
type2val2
1
ef
type1val2
type2val2
What I want to do is group these by their id and get the following o/p
ab,cd,ef type1val1 type2val1
ab,cd,ef type1val2 type2val2
The grouped values need to be separated by commas.
What I'm doing currently:
I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p.
Can anybody help me out?
Thanks,
Subha
I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows.
My table structure is:
create table #scoresheet
(Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255),
Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255),
Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255));
I would like to have three rows with four columns.
What I've done so far is create a stored procedure that uses a table variable:
create procedure sp_splitsinglerow as
declare @Scoresheet_rows_table_var table (
ReviewArea varchar(25),
NumericalValue int,
VI varchar(10),
Comments nvarchar(255));
insert into @Scoresheet_rows_table_var
(ReviewArea, NumericalValue, VI, Comments)
select Decisions, DNumericalValue, DVI, DComments
from #scoresheet
The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.
Any ideas on how to achieve that?
BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)
I have a table which looks like
Low High
-------------------------
cx01 cx04
sn05 sn08
I need output like
Result
-------------
cx01
cx02
cx03
cx04
sn05
sn06
sn07
sn08
How to get this output using sql query?
I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.
But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.
This is a small example of what the rows look like in the resultset, and what I want to single to be.
DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int
[Code] ....
I am not sure how to write the query to have all the data in a single row.
I’m trying to return data in a single row.Here’s what my table looks like:
Employee #,Hours Type,Total Hours
1234,Regular,40
1234,OT,8
1234,Regular,36
[code]....
I need the results of my query to total each hours type and group together:
EmpNo,Sum Of Regular, Sum of Overtime,Sum of Doubletime
1234,76,19,12
7777,45,8,5
I don’t know how to get the data returned in a single row.
Hi
I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner
(
@Crime_No varchar(20),
@Unit_ID int
)
as
begin
DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int
DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
SELECT
TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1,
dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id,
dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone,
dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,
TBL_TR_ACCIDENT_VEHICLE.Registration_Number,
TBL_TR_ACCIDENT_VEHICLE.Crime_No,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre,
TBL_TR_ACCIDENT_VEHICLE.vehicle_Make,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model,
TBL_TR_ACCIDENT_VEHICLE.Unit_ID,
TBL_TR_ACCIDENT_VEHICLE.RowID,
TBL_TR_ACCIDENT_VEHICLE.UserID,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color,
TBL_TR_ACCIDENT_VEHICLE.HP,
TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity,
TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle,
TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight,
TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight,
TBL_TR_ACCIDENT_VEHICLE.Skid_Length,
(select TBL_TR_Person_OutsideDetails.OutSide_state from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem-
/*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5))
FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE
WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number)
select @Defect_ID
FROM
tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE
where
tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
AND
TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No
AND
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
end
GO
I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
[code]....
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
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
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
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
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 RelatedI know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order.
Code:
update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111
PHP Code:
$qt="update categories set ";
for($i=0;$i<$num;$i++){
$a=$i+1;
$qt.="sortorder=$i";
if($a<$num){
$qt.=", ";
}
}
$qt.=" where userid=111";
Using PHP I can amend the loop above to slot in a row I want so I can change the sort order.
unfortunately I'm not sure how to build such a query in mssql, can anyone help?
Hi,
I need to return multiple rows into one single string
Declare @String varchar(1000)
Create table Cus (CusId Int,CusName varchar(10))
Insert into Cus Select 1,'John'
Union All
Select 2,'Bob'
Select * from Cus returns
1John
2 Bob
I need to return the all the rows from Cus table into a single string. The return is dynamic.
I do not know the number of rows returned
My result should be
@String = 1,John,2,Bob
How can i do that ?
With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.
;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name