SQL 2012 :: Split Column Data Into Multiple Rows

Apr 1, 2015

How to split a column data into multiple rows, below is the requirement...

Create table #t3 (id int, country (varchar(max))

INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'

Output should be like below

1 ,AU-Australia
1,MM-Myanmar
1,NZ-New Zealand
1,PG-Paua New Guinea
1,PH-Phlippines

Note: we are getting source data from sqlserver tables.

I googled and found below way but did't get the output as required

SELECT A.id, a.country,
Split.a.value('.', 'VARCHAR(500)') AS String
FROM (SELECT id, country ,
CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String
FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

View 4 Replies


ADVERTISEMENT

Split A Row Into Multiple Rows Based On Column

Sep 5, 2014

I need to split a row into multiple rows based on multiple column values.

time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00

I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.

row 1
time_in 10:00
time_out 12:00

row 2
time_in 13:00
time_out 14:00

row 3
time_in 15:00
time_out 16:00

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

Split Column Data Into Multiple Lines

Jan 2, 2008



Hi,
I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below .

"FTW*Christopher,Lawson
FTW*Bradley,James"


Please let me know how can I acheive this?

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

Getting Data From Multiple Rows Into One Column

Dec 3, 2005

I have a table that has values as follows:PersonID Degree55 MD55 Phd55 RN60 MD60 PhdI need a create a query that will give me output like this:PersonID Degree55 MD, Phd, RN60 MD, PhdAny ideas

View 16 Replies View Related

SQL 2012 :: Split Row Into Three Or More Rows?

Feb 26, 2014

I am trying to split a row that currently has information like this:

area: north,south,west

however I want the area column to display the information like this:

Area:
North
South
West

I have wrote a split function and it worked fine in other work I did...

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

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

Split One Field In Multiple New Rows

Nov 27, 2007

HiHo,
just a beginners question:

I have the following row with 2 fields:

Field 1: Task A
Field 2:´ 1;2;3;4

The number of semicolon divided elements in Field 2 is variabel.

I would like to create new rows like:

Row 1 Field 1: A Field 2: 1
Row 2 Field 1: A Field 2: 2
Row 3 Field 1: A Field 2: 3
Row 4 Field 1: A Field 2: 4


I think I should use a Foreach Loop.
But I don't exactly how to do it?

best regards
Chris

View 5 Replies View Related

Help With Combining Data From Multiple Rows Into One Column In A View

Jul 19, 2007

Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300

View 1 Replies View Related

Split One Row Into Multiple Rows Based On Time Elements

Feb 5, 2007

I'm dealing with a problem.

The record information example

DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

what i want is for every half hour between start and end a record

10.30 action1
11.00 action1
11.30 action1

how can i create this, i'm a little stuck on this

View 2 Replies View Related

Split One Column Into Multiple Columns

Jan 28, 2008

Hi all,
I have a requirement like this  ,
I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469
I have to split this into 3 more columns like(Address1,name,phoneno)--
Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)
 
Example:
Address:Rajkot
Address1:Univercity Road
Name:Mr. K KK Tank Guntur Jal Bhavan
PhoneNO:9843563469
 
How can i acheive this one with out data lose in Address Column.
Thanks in advance.
 
 
 

View 2 Replies View Related

How To Split A Delimited Column Into Mulitple Rows In The Dataflow?

Jan 19, 2006

I'm sure there is probably a very easy solution that I am just not seeing or can't Google...

I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?

TIA,
Matthew

View 1 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

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

SQL Server 2008 :: How To Split Time Column Values Into Rows

Jun 6, 2015

I have the table as

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I want the output as : -

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I am trying the below mentioned code but it is not giving me the desired output..

with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn

[Code] .....

I am getting wrong output as -

| start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00

View 1 Replies View Related

SQL Server 2008 :: Joining Two Tables - Split Rows Into Column

Sep 29, 2015

I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.

This how the data is

Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample

This is what is expected...

Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')

[Code] ....

How I can implement this ? I believe if there are any numbers I need to split into two columns .

View 2 Replies View Related

Transact SQL :: Split Column To Rows Which Is Delimited Using Commas And Semicolons

Jul 9, 2015

All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.

Data in Tables

Test1, Test2, Test3
Test4;Test5;Test6

Desired output
Test1
Test2
Test3
Test4
Test5
Test6

Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.

View 7 Replies View Related

How To Split Data Into Two Rows

Jun 22, 2007

I have a query that returns a table similar to:

State Status Count
CA Complete 10
CA Incomplete 200
NC Complete 20
NC Incomplete 205
SC Incomplete 50


What sort of query will allow me to reformat the table into:

State Complete Incomplete
CA 10 200
NC 20 205
SC NULL 50

View 5 Replies View Related

Split A Column String-value Into Multiple Columns / Inserting In Another Table

Aug 14, 2012

I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.

Old Table:

Code:
ID, StringValue

New Table:

Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.

I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.

Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable

Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?

Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1

[code]....

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

Integration Services :: SSIS - Split Multi Value Column Into Multiple Records?

Oct 12, 2014

I have one scenario

Table
Col1. Col2
1. A,b,c,df,ghf
2. C,b
3. B

Output should be

Col1. Col2
1. A
1. B
1. C
1. Df
1. Ghf
2. C
2. B
3. B

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

SQL Server 2012 :: Normalize Values From Column Split Using Tally Table?

Apr 23, 2014

I have a table that has the following structure:

EntryID int,
Categories varchar(200)

values look like:

541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'

what I want to do is extract the Categories column to a normalized separate table:

541,'A'
541,'B'
541,'C'
345,'B' ....

I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?

View 2 Replies View Related

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

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

Split Data In Column

Mar 24, 2006

hai all,
This is my first question to this forum.
here is my situtation:
I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query


Database : sql server
tabel name :job_allocations
column naME :technicain code

Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician.

Based on the technician code user chooses column will be updated
if single data will be TC01
if more than one then data will be TC01:TC02:TC03

user can choose any number of techincian for a job

MY problem is :How to split tha when there is multiple technician and calculate cost for the job
Ineed it in single excecution query

Table structure

job_allocation table

jobcardn_fk Technician_code
jc01 TC01
jc02 Tco1:Tco2:Tc03......


I need it in



jobcardno_fk TEchnician_code
jco1 Tc01
jco2 Tc01
jco2 TC02
jc02 Tc03




TKs ands Regards
Diwakar.R

View 1 Replies View Related

Concatenate Column Value From Multiple Rows Into A Single Column

Feb 27, 2008

Hello,

I need to concatenate a column from multiple rows into a single column in a new table.

How can I do this?

SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR


Need to have

SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR


THANK YOU

View 10 Replies View Related

Split Data Into Two Column Table

Mar 13, 2007

Hello all,

Little layout question. Assume my dataset returns the following data:

A

B

C

D

E



How can I present this data in a table (or list, or matrix) splitted into two columns:

A B

C D

E



Any idea will be very appreciated! Thanks a lot!

TG

View 4 Replies View Related

Transact SQL :: Split Column Data To Row

May 19, 2015

I found string from net how to split column data to row

SELECT A.JbIDFull, A.ProdID, A.OrderQty, A.OtherDetails, A.OrderDate, c.Item FROM
(SELECT JbIDFull, DeptID, ProdID, OrderQty, OtherDetails, OrderDate, LamORSteachType, JBID, RMIDs, RMQty
FROM tblJobCardforProduction WHERE JBID = '2' AND DeptID = '3') A
CROSS APPLY dbo.SplitStringNEW(a.RMIDs,';') b
CROSS APPLY dbo.SplitStringNEW(a.RMQty,'/') c

When I apply one cross apply it's working fine but when i apply for one more column is replacing three time one row, this data i want to split RMIDs and RMQty with same JbIDFull

JbIDFull   DeptID    ProdID OrderQty     RMIDs RMQty

PD-May15-00001 3 2044 10000     PROD-00052 0
PD-May15-00002 3 921 1000 PROD-00052;PROD-00383;PROD-00384 500;600;700

View 2 Replies View Related

SQL 2012 :: 500GB Data Warehouse - How To Split It Into File Groups

Aug 21, 2014

OK, so I have:

- 500 GB DW
- 5 GB in smaller DBs
- 220 GB TempDB
- 350 GB in Log files.

My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.

I have several IO locations:

- 540 GB in-server HDD 15k RAID10
- 1 TB HDD 15k RAID10 on SAN (separete controller)
- 2 TB HDD 15k RAID10 on SAN (same controlller as below)
- 800GB SSD RAID10 on SAN (same controller as above)

Data warehouse has 2 fact tables that are absolutely crucial and quite large.

Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:

- FILEGROUP1: for 1st crucial Fact Table
- FILEGROUP2: for 2nd crucial Fact Table
- FILEGROUP3: for tempDB
- FILEGROUP4: for dimensions data
- FILEGROUP5: for the rest of facts data
- FILEGROUP6: for dimensions indexes
- FILEGROUP7: for the rest of facts indexes
- FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
- FILEGROUP9: for the rest of log files
- FILEGROUP10: others

How should I organize them across available drives? I was thinking about sth like:

800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10
540 GB in-server: FILEGROUP 9

I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.

Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.

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







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