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

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

Concatenate Multiple Rows In One Row

Mar 11, 2008

i have a table

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

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

Concatenate Multiple Rows Into One Column?

May 7, 2008

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like the query result to look like this:1....First Assignment....05/01/2008....553422....Second Assignment...05/03/2008....33456,523433....Third Assignment....05/07/2008....35225,45121,11553Any suggestions would be appreciated!

View 5 Replies View Related

Concatenate One Field From Multiple Rows?

Oct 18, 2006

I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:

Title - Author - Edition
The Amazing Pixies - A. N. Author - Hdbk
The Amazing Pixies - A. N. Author - Pbk

Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.

View 2 Replies View Related

Concatenate Multiple Rows Into One Column?

May 7, 2008

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:

TableParent (ASSIGNNUM (PK), DESC, STARTDATE)
TableChild (ASSIGNNUM (FK), EMPLOYEENUM)

There could be multiple employees for each assignment. Sample data:

TableParent
1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008

TableChild
1....55342
2....33456
2....52343
3....35225
3....45121
3....11553

I would like the query result to look like this:

1....First Assignment....05/01/2008....55342
2....Second Assignment...05/03/2008....33456,52343
3....Third Assignment....05/07/2008....35225,45121,11553

Any suggestions would be appreciated!

View 12 Replies View Related

How To Concatenate Multiple Rows Into One Field?

Jul 20, 2005

Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri

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

Merge Multiple Rows Into A One Or More Rows With Multiple Columns

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

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

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 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 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

SQL Server 2012 :: Query With Multiple Columns - How To Get Next Value One After Another

Aug 5, 2015

I want to know if it is possible to do the following;

I have patients that may have been transferred to different locations(see below)

location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02

i know i can do a min max to get my first and last values. I want to label the columns something like

1st location, 2nd location, 3rd location, 4th location, discharge location.

there could be 1 location or 20.

is there a way to do this?

i can do a temporary table and then an update query to add the values to those columns.

just not sure how to get the next value and then the next etc.

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

SQL Server 2012 :: Combining Multiple Rows Into One

Feb 4, 2015

How I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together.

I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.

00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000

[Code] ....

View 7 Replies View Related

SQL Server 2012 :: QEURY - Join Multiple Columns

Sep 1, 2015

DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))

INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL

[Code] ....

Need query to get the Result as below

PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL

By using case statement we can retrieve the data, but looking for alternate way to do it.

View 4 Replies View Related

SQL Server 2012 :: Combining Multiple Rows Into One Field

Feb 26, 2015

I am needing to combine the Notes field where Number and date are the same...For example

for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.

CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Error When Passing Multiple Rows

Apr 20, 2015

I am getting error when I passed multiple rows in less than condition:

create table #t1
( ID int)
INSERT INTO #t1

SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8
CREATE TABLE #t2
(ID int)
INSERT INTO #t2
SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4

SELECT ID FROM #t2
WHERE ID < (SELECT ID FROM #t1)

Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to pass multiple values in this condition?

View 2 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

SQL Server 2012 :: Splitting A Single Column Into Multiple Columns?

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

SQL Server 2012 :: Create View With Exploding Rows Into Multiple

Apr 28, 2014

I have a table like below:

ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3

Now I want to create View that will have an exploded resultset based on SrvType.

For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.

I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.

View 4 Replies View Related

SQL Server 2012 :: Convert Multiple Rows To One Row Separated By Delimiter?

Jun 2, 2014

I am running sqlserver 2012.

My output data is like

COL1
aaa
bbb
ccc

Now, i want to convert the output to

aaa;bbb;ccc

How can i do this?

View 2 Replies View Related

SQL Server 2012 :: Trigger Inserted Multiple Rows After Insert

Aug 6, 2014

I create a Trigger that allows to create news row on other table.

ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS

[code]...

But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001

I search a means to have 50 lines differently on my CONTACT table.

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005

View 9 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 :: Splitting Column Values In Multiple Columns And Assigning It To Row

Dec 11, 2013

How do I write a query using the split function for the following requirement.I have a table in the following way

Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3,4,6
2 Test2 3,4,5 300,455,600 2,3,8

I want an output in the following format

Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8

If you see the data, first element in col1 is matched to first element in col2 and 3 after splitting the string.

View 2 Replies View Related

SQL Server 2012 :: Parse Two Delimited Table Columns Into Multiple Records

Oct 22, 2014

I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.

CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)

[Code] .....

The results I am looking to achieve are:

PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4

Is there an SQL statement that can be used to accomplish this?

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