SQL Server 2008 :: Combine Multiple Rows To Single Row?

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


ADVERTISEMENT

Combine Multiple Rows To Single Column

Jul 8, 2014

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

View 6 Replies View Related

Combine Multiple Rows Into Single SQL Record

Jan 28, 2008

Hello:

I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.

productID SectionID statusID
10 1 0
10 2 1
10 3 2
10 4 1
10 5 3
10 6 1
11 1 0
11 2 1
11 3 2
11 7 3
11 8 3

Need to return two rows with the respective values for each section.

productID section1 section2 section3 section4 section5 section6 section7 section8
10 0 1 2 1 3 1
11 0 1 2 3 3

Any information or if you can point me in the right direction would be appreciated.

Thanks

View 4 Replies View Related

Script To Combine Multiple Rows Into 1 Single Row

Dec 22, 2006

Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?

View 7 Replies View Related

Script To Combine Multiple Rows Into A Single Row

Jul 20, 2005

Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance

View 1 Replies View Related

SQL Server 2008 :: Merge Multiple Rows Of Same ID Into Single Row

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

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

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

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View 2 Replies View Related

Combine Multiple Records Into Single Row

Mar 29, 2007

This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.

View 13 Replies View Related

How Can I Combine Values Of Multiple Columns Into A Single Column?

Oct 8, 2007



Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888

And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888

where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?

View 1 Replies View Related

SQL Server 2008 :: Creating Rows Between Dates In Single Statement

Apr 21, 2015

I am trying to find an easy way to create multiple of just two date in a single sql statement.

E.G.

A statement using the parameters

@StartDate = '2015-01-01'
@EndDate = '2015-01-05'

Ends up with rows:

'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'

What would be the best way to do this ?

View 3 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

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

Combine Multiple Rows Into One

Feb 7, 2008

Hello,

I have a delima, and im not really sure if this possible. But i have a table like lets say

id | data1
1 this
2 that
3 stuff

i want to be able to return this as one row with the data from data1 in one column seperated by commas.

so the result would be

1 Column
this, that, stuff

can anyone help me with this.

Thank you,

~ Moe

View 7 Replies View Related

Combine Data From Multiple Rows

Feb 5, 2015

I am running a query to pull data from 2 tables. However multiple data elements could be attached to one unique ID which when I run the query it repeats causing the entire data set to give inaccurate numbers. How to achieve this:

xxx.001A3264
xxx.001A3685
xxx.002A3261
xxx.002A3685

I would like my results to look like this:

xxx.001A3264 & A3685
xxx.002A3261 & A3685

View 2 Replies View Related

Combine Matching Multiple Rows Into One Row

Jul 23, 2005

IS there a way to combine all matching rows in a table so that itoutputs as one row, for example:tblMyStuffUniqueID int IDENTITYParentID intSomeSuch nvarchar(50)SomeSuch2 nvarchar(50)Table data:UniqueID ParentID SomeSuch SomeSuch21 1 Dog Bark2 1 Cat Meow3 3 Cow Moo4 3 Horse Whinnie5 5 Pig OinkDesired query result from Query:SELECT ??? as myText from tblMyStuff WHERE ParentID = 3myText = Cow Moo, Horse WhinnieHelp is appreciated,lq

View 2 Replies View Related

Transact SQL :: Combine Multiple Rows In A New Column

Jul 16, 2015

I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.

DECLARE @M31
( M31_SQL_ID INT
,JOB_NUMBER INT
,LINE_NUMBER INT
,WORKS_DESC VARCHAR)

[Code] ...

Output should be as below

219242
16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back  

219245
16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...

View 3 Replies View Related

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 View Related

SQL Server 2012 :: Assigning Multiple Rows To A Single Variable Parameter

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

SQL Server 2008 :: Data Conversion - Merge Multiple Columns Into Single Column Separated By Semicolons

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

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

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

SQL Server 2012 :: Statement To Group Rows As Multiple Child Under Single Parent?

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

SQL Server 2008 :: Concatenating Multiple Rows (Certain Columns)

Jan 29, 2015

Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.

current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015

Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015

View 4 Replies View Related

SQL Server 2008 :: How To Select Multiple Rows Delimited With Comma

Oct 6, 2015

I have a table like this :

ID Description ParentID Level
B.01 Parent 1 H
B.01.01 Parent 1.1 B.01 H
B.01.01.01 Parent 1.1.1 B.01.01 H
B.01.01.01.01 Detail 1 B.01.01.01 D
B.01.01.01.02 Detail 2 B.01.01.01 D

[Code] .....

That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.

View 2 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

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

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

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

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

SQL Server 2008 :: Split Varchar Variable To Multiple Rows And Columns Based On Two Delimiter

Aug 5, 2015

declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'

---------------------

Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)

select * from #tmp

I want to convert to @var to same like #tmp table ..

"@" - delimiter goes to rows
"~" - delimiter goes to columns

View 6 Replies View Related

Single Row Into Multiple Rows

May 8, 2008

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.







View 1 Replies View Related

Getting Multiple Rows In A Single Row

Jan 21, 2008

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

View 4 Replies View Related

Spliting Single Row Into Multiple Rows

Sep 20, 2006

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. :)

View 2 Replies View Related

Split Single Row To Multiple Rows

Feb 27, 2012

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?

View 8 Replies View Related

T-SQL (SS2K8) :: Multiple Rows Into A Single Row

Nov 7, 2014

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.

View 2 Replies View Related

Return Multiple Rows As A Single Row

Oct 8, 2014

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.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved