MS SQL Server :: Merge Multiple Rows Into A One Or More Rows With Multiple Columns

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

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

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

Apr 21, 2015

View 6 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

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

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

Apr 23, 2008

Hello All,

I am rather new to reporting on SQL Server 2005 so please be patient with me.

I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.

For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report

The table (System Info) looks like:-

ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |

Which

So basically I need it to look like this.

Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy

So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1

How would I go about displaying the other system parts as columns with info

Any help is much appreciated!

View 3 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

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

Parent Table - (multiple) Rows Into (multiple) Columns

Feb 12, 2015

I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:

I need results for each parent like this

ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]

View 1 Replies View Related

Processing Multiple Rows And Multiple Columns In SSIS

Sep 26, 2007

I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!

View 1 Replies View Related

How To Merge Multiple Rows Into Single Row

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

Multiple Rows To One Row And Multiple Columns

Sep 7, 2007

Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:

This is the how the table is:
EmpID Designation

678
CFA

679
CFA

680
CFA

685
CFP

685
CIMA

685
IMCA

I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA

could anyone provide some help on this?

Thanks

View 1 Replies View Related

SQL Server 2012 :: Merge Multiple Rows Into One?

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

SQL Server 2012 :: How To Merge Multiple Rows Into One Row

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

T-SQL (SS2K8) :: Merge Multiple Rows In Single Row

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

DB Engine :: Merge Multiple Rows Into Single Row

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

Transact SQL :: Merge Multiple Rows Onto A Single Row

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

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

Multiple Rows And Columns MAX

Jul 3, 2006

Hi All,I'm using the sample table:ID | CODE | V1 | V2 | V3----------------------------------------------------------------1 3 10 3 431 4 9 8 221 2 6 2 551 5 57 12 6I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)and their respective CODEs.For the above table the returned record for ID=1 should be:v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2currently I've got a very messy solution, I'mlooking for an elegant way to do this.10x,Assaf.

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 - Multiple Rows Into Columns

Jan 27, 2004

Hello!

I am trying to figure out if there is a way to convert (unlimited numberof) rows into columns in SQL Server? Can this be done using a SQL query and without creating any extra tables or procedures?

Please let me know.

Thanks much.

-PV

View 5 Replies View Related

How To Split Columns Into Multiple Rows

Jun 7, 2008

SOURCE TABLE
ID DESCRIPTION
1 I am a programmer
2 I am a doctor

Destination Table

ID LINE DESCRIPTION(Varchar10)
1 1 I am a pro
1 2 grammer
2 1 i am a doc
2 2 tor

Please someone help me on this.

View 4 Replies View Related

Summing Multiple Rows In Columns

Jul 9, 2014

I am working with a MS SQL database associated with SCCM 2007. SCCM collects software product usage data, and I am tasked with generating a report that will return results between two user-chosen date ranges. I set up prompts for month, year, endmonth, endyear. They would enter 2, 2014, 4, 2014 for example. Timekey would equal 201402, endtimekey = 201404. The tables I am concerned about look like this:

v_MonthlyUsageSummary

TimeKey ResourceID FileID UserID UsageCount TSUsageCount UsageTime LastUsage
201402108343 253362240038122 0 2550272014-02-28 13:29:39.000
201402108340 253362240665 2 0 122014-02-27 15:58:13.000
201402108932 253362231708 0 10 216702014-02-14 20:41:35.000
201403124035 504006542145 15 0 1756292014-03-31 19:37:37.000
201403124036 504006537579 46 0 7737112014-04-01 00:00:00.000

v_SummarizationInterval

TimeKey IntervalStart
2013102013-10-01 00:00:00.000
2013112013-11-01 00:00:00.000
2013122013-12-01 00:00:00.000
2014012014-01-01 00:00:00.000
2014022014-02-01 00:00:00.000
2014032014-03-01 00:00:00.000
2014042014-04-01 00:00:00.000
2014052014-05-01 00:00:00.000
2014062014-06-01 00:00:00.000
2014072014-07-01 00:00:00.000

The problem is I only know how to grab one timekey, but I need to combine multiple timekeys to sum up the usage counts for each workstation.

Here is the full query.

declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=100*@Year+@Month
set @endTimeKey=100*@endYear+@EndMonth
select @months=DATEDIFF(d,@timekey,@endTimeKey)

[Code] .....

View 20 Replies View Related

Tranform Columns To Rows With Multiple Values

Sep 30, 2006

I have the following result set:

Code:

NameCode1Value1Code2Value2
A1020020250
B20300NULLNULL
CNULLNULLNULLNULL

I want to transform the columns into rows like this:

Code:

NameCodeValue
A10200
A20250
B20300

Any suggestions?

View 1 Replies View Related

Transact SQL :: How To Break Rows Into Multiple Columns

Jul 14, 2015

I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.

Row data
ID
1
2
3
4
5
6
7
8
9
...
98
99
100

Output will be like
c1 c2   c3    c4    c5....
1    5    9      13
2    6    10    14
3    7    11    15
4    8    12    16

View 4 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows And Columns Into Single Row

Apr 25, 2014

I have this query

SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code

and the output

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10

How can I modify the query to come up with output below,

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10

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

Union Join To Return Multiple Rows Into Columns.

Feb 19, 2008

I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.

101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300

I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.

101 mem 100 charity 200 chi 300

Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.

SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0

union all

SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0

union all

SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0

) AS p
GROUP BY p.id

View 9 Replies View Related

PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns

Aug 3, 2007

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:

CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount

1
1
Panel
Jan
5
10
50

1
1
Panel
Feb
5
15
75

1
1
Panel
Mar
5
20
100

1
2
Frame
Jan
10
30
300

1
2
Frame
Feb
10
25
250

1
2
Frame
Mar
10
20
200

1
3
Glass
Jan
20
10
200

1
3
Glass
Feb
20
20
400

1
3
Glass
Mar
20
30
600

I would like it to be converted into following result set:

CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar

1
1
Panel
5
10
50
15
75
20
100

1
2
Frame
10
30
300
25
250
20
200

1
3
Glass
20
10
200
20
400
30
600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

View 1 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

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

Reporting Services :: How To Create Report With Multiple Rows With One Parent And Multiple Child Groups

Aug 17, 2015

I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.

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

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

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

View 3 Replies View Related