January 11, 2017 at 8:34 am
I have a column something like below
Number
14522345
15235642
16289563
16287423
30245123
31451831
32452632
31458142
I need to split into two columns
Number A Number B
14522345 30245123
15235642 31451831
16289563 32452632
16287423 31458142
January 11, 2017 at 8:36 am
wweraw25 (1/11/2017)
I have a column something like belowNumber
14522345
15235642
16289563
16287423
30245123
31451831
32452632
31458142
I need to split into two columns
Number A Number B
14522345 30245123
15235642 31451831
16289563 32452632
16287423 31458142
Okay, so how do we know which record starts the list of numbers for column B, or does it even matter? Do you just want to take the numbers and put them in two columns? Or, do you need to match a given number with a very specific other number?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 11, 2017 at 8:39 am
The column has values starting with 1 and 3 as sent above.
The column should be split into two
Rows starting with 1 into column A
Rows starting with 3 into column B
January 11, 2017 at 8:42 am
How do we determine how these value should be paired?
In your example you had:
14522345 30245123
Why not any of these?
14522345 31451831
14522345 32452632
14522345 31458142
What's the logic there?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 11, 2017 at 8:48 am
To be more specific this is what I mean
Column
14522345
15235642
16289563
16287423
30245123
31451831
32452632
31458142
Column AColumn B
1452234530245123
1523564231451831
1628956332452632
1628742331458142
January 11, 2017 at 8:53 am
wweraw25 (1/11/2017)
I have a column something like belowNumber
14522345
15235642
16289563
16287423
30245123
31451831
32452632
31458142
I need to split into two columns
Number A Number B
14522345 30245123
15235642 31451831
16289563 32452632
16287423 31458142
Try this:
CREATE TABLE #NUMBERS (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Number int
);
INSERT INTO #NUMBERS (Number)
VALUES(14522345),
(15235642),
(16289563),
(16287423),
(30245123),
(31451831),
(32452632),
(31458142);
WITH PREFIX_1 AS (
SELECT 1 AS START_DIGIT, N.ID, N.Number AS NumberA,
ROW_NUMBER() OVER(ORDER BY N.ID) AS RN
FROM #NUMBERS AS N
WHERE LEFT(CAST(N.Number AS varchar(10)), 1) = '1'
),
PREFIX_3 AS (
SELECT 3 AS START_DIGIT, N2.ID, N2.Number AS NumberB,
ROW_NUMBER() OVER(ORDER BY N2.ID) AS RN
FROM #NUMBERS AS N2
WHERE LEFT(CAST(N2.Number AS varchar(10)), 1) = '3'
)
SELECT P1.NumberA, P3.NumberB
FROM PREFIX_1 AS P1
FULL OUTER JOIN PREFIX_3 AS P3
ON P1.RN = P3.RN
ORDER BY COALESCE(P1.RN, P3.RN);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 11, 2017 at 9:01 am
wweraw25 (1/11/2017)
To be more specific this is what I meanColumn
14522345
15235642
16289563
16287423
30245123
31451831
32452632
31458142
Column AColumn B
1452234530245123
1523564231451831
1628956332452632
1628742331458142
First things first. When you post question about sql you need to provide details in a way that allows others to help. This is generally done by providing some ddl and sample data. Here is an example of how this should look in your case.
create table #Something
(
SomeValue int
)
insert #Something(SomeValue)
values
(14522345),
(15235642),
(16289563),
(16287423),
(30245123),
(31451831),
(32452632),
(31458142)
select *
from #Something
drop table #Something
The next thing we need to see is the desired output. You did a great job posting this information. However, you seem to have a certain order to the output but there is nothing in the data you can use to sort your rows like this. Remember that in relation theory a table is an unordered set. If you want order you MUST provide the rules for the order. Also, what would you do if there are more rows that start with 3 than there are that start with 1? Or the opposite?
Assuming you want a NULL on either side if there is not a corresponding match you could do something like this.
select s1.SomeValue
, s2.SomeValue
from
(
select SomeValue
, ROW_NUMBER() over (order by (select null)) as RowNum
from #Something s
where CONVERT(varchar(20), SomeValue) like '1%'
) s1
full outer join
(
select SomeValue
, ROW_NUMBER() over (order by (select null)) as RowNum
from #Something s
where CONVERT(varchar(20), SomeValue) like '3%'
) s2 on s2.RowNum = s1.RowNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2017 at 9:02 am
Haha. Steve was posting almost the exact same query while I was posting. Well done sir!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2017 at 1:56 pm
Sean Lange - Wednesday, January 11, 2017 9:02 AMHaha. Steve was posting almost the exact same query while I was posting. Well done sir!!
Thank you kind sir!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 17, 2017 at 5:21 pm
wweraw25 - Wednesday, January 11, 2017 8:34 AMI have a column something like belowNumber1452234515235642162895631628742330245123314518313245263231458142I need to split into two columnsNumber A Number B14522345 3024512315235642 3145183116289563 3245263216287423 31458142
Now that you have a couple of answers that seem to do the trick, let me ask... will the numbers ALWAYS start with "1" or "3"?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2017 at 5:24 pm
wweraw25 - Wednesday, January 11, 2017 8:48 AMTo be more specific this is what I meanColumn1452234515235642162895631628742330245123314518313245263231458142Column AColumn B1452234530245123152356423145183116289563324526321628742331458142
How is that any more specific than the original post (it's not) and how does that answer the question asked (it doesn't). 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply