Trying To Split My Columns Into Years (col1 Must Have Vals For 2005, Col2 Vals For 2006)
Jan 26, 2007
Hi, i'm reasonably new to reporting services and am looking for a way to split my reports' Years to compare the months in year 2005 to 2006 but i can't get my data nest to one another in a single line, it splits the years into different rows
as an example this is what i want if you can decipher that
2005
2006
Growth
2005 Year to Date
2006 Year to Date
Year to Date Growth
turnover
gross profit
turnover
gross profit
turnover
gross profit
turnover
gross profit
Jan
250500
75300
280200
84100
11.85629
11.686587
250500
75300
Feb
205000
67950
190350
59900
-7.14634
-11.84695
455500
143250
take the month above and add the
current months values
Mar
217670
70540
234200
78000
7.594064
10.57556
673170
213790
Apr
270780
84000
290400
93000
7.245735
10.714286
943950
297790
May
265000
79260
289050
90200
9.075472
13.802675
1208950
377050
Jun
277300
81050
277900
82000
0.216372
1.172116
1486250
458100
Jul
Aug
Sep
Oct
Nov
Dec
Here is my Query:
SELECT /*DT.[YEAR],*DT.[MONTH],*DT.MONTH_NAME,*/ DC.CLIENT_KEY, (select SUM(FT.Cost)where dt.[year] = 2005) AS COST , (select SUM(FT.Price)where dt.[year] = 2005)AS SALES,(select SUM(FT.Cost) where dt.[year] = 2006),(select SUM(FT.Price) where dt.[year] = 2006)--, SUM(FT.QTY) AS QUANTITY, SUM(FT.PRICE) - SUM(FT.COST) AS GP,(SUM(FT.PRICE) - SUM(FT.COST)) / SUM(FT.PRICE) * 100 AS GP_PERCENTAGEFROM FACT_TRANSACTION FT, DIM_TIME DT, DIM_CLIENT DC, DIM_INVOICE_TYPE DIT, DIM_PRODUCT DPWHERE FT.TIME_KEY = DT.TIME_KEYAND FT.PRODUCT_KEY = DP.PRODUCT_KEYAND FT.CLIENT_KEY = DC.CLIENT_KEYAND FT.TYPE_KEY = DIT.TYPE_KEY AND DIT.TYPE_KEY NOT IN (5,6,13,14,15,16,17)AND DC.CLIENT_SERIALNO = '86634'--AND DT.[YEAR] IN(2005,2006)AND DT.[MONTH] IN(1,2,3,4,5,6,7,8,9,10,11,12)AND DP.PRODUCT_KEY <> 1668684GROUP BY DT.[YEAR],DC.CLIENT_KEY--, DT.[MONTH]ORDER BY /*DT.[YEAR],*/DT.[MONTH]
but it returns everything under one another
2005 1 January 2005 3 296092.3431 405263.62 12811 109171.2769 26.93
2005 2 February 2005 3 318597.658 432098.17 13220 113500.512 26.26
2005 3 March 2005 3 371327.721 506481.46 15283 135153.739 26.68
2005 4 April 2005 3 371647.994 504713.99 15491 133065.996 26.36
2005 5 May 2005 3 400870.6138 542759.57 16296 141888.9562 26.14
2005 6 June 2005 3 399673.0086 546110.59 16607 146437.5814 26.81
2005 7 July 2005 3 390477.7521 535531.40 16153 145053.6479 27.08
2005 8 August 2005 3 380628.57 520281.87 15800 139653.30 26.84
2005 9 September 2005 3 340949.8849 471861.17 14820 130911.2851 27.74
2005 10 October 2005 3 340240.804 470007.78 14444 129766.976 27.60
2005 11 November 2005 3 349156.1871 481193.61 14523 132037.4229 27.43
2005 12 December 2005 3 346038.5059 477011.72 14865 130973.2141 27.45
2006 1 January 2006 3 340062.1369 470010.08 14037 129947.9431 27.64
2006 2 February 2006 3 328463.9689 452404.79 13996 123940.8211 27.39
2006 3 March 2006 3 375264.977 517800.27 16065 142535.293 27.52
2006 4 April 2006 3 412708.965 567014.52 17550 154305.555 27.21
2006 5 May 2006 3 446973.4231 606476.26 18920 159502.8369 26.29
2006 6 June 2006 3 406072.4943 544634.77 17053 138562.2757 25.44
2006 7 July 2006 3 389104.6316 526091.14 16228 136986.5084 26.03
2006 8 August 2006 3 317810.4531 431530.58 13641 113720.1269 26.35
2006 10 October 2006 3 405230.7083 549310.72 17151 144080.0117 26.22
2006 11 November 2006 3 379788.6645 514554.14 15917 134765.4755 26.19
2006 12 December 2006 3 393235.0906 531582.69 16924 138347.5994 26.02
If i do get them split then it put every year's value on a different line
2005 1234123.34 32432432.43 NULL NULL
2006 NULL NULL 12312.212 15235453.21
Please Help,
View 1 Replies
ADVERTISEMENT
Jun 27, 2007
For a table 'table1' in sqlserver 2000,
table1(col1) is clustred index 'Ix1' and table1(col1,col2,col3) is nonclustered index 'ix2'
is not tabel1(col1) 'ix1' a duplicate/redundant index ?
which index should be retained ?
or should both indexes remain on the OLTP table ?
Thanks a lot in advance.
View 1 Replies
View Related
Feb 29, 2008
I've got an access table with about 2 million rows. I'm using this to update a table in SQL that holds pretty much the exact same data, only with an added Identity column.
From week to week, the access table grows. For example, next week it may have 2.1 millions rows, the week after 2.2 million, etc.
The goal of the DTS is to keep the SQL table up to date w/ the access one. In the past, this has been done by deleting everything from the SQL Table and then importing the ENTIRE access table. This not only takes more time then need be, since the majority of the records *already* existed, but it also threw referential integrity out the door - other tables should be referencing the Identity in the SQL Table. IDEALLY, the only rows that would be transferred from the access file are ones that don't already exist in the SQL table.
I don't want to re-invent the wheel, and have to confess being a little under-schooled on all that SSIS has to offer. Is there a Data Flow Transformation that would solve this?? Any other advice? If all else fails, I'd probably just dump the entire access table to a temp table and then insert vals into the production table that don't exist, but even this would require more temp hard drive space then I'd like.
Thanks!
View 4 Replies
View Related
Sep 11, 2006
Hi,
Can anybody help me to create a single query? I have this problem.
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)
INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)
I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)
(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50
Any help would be greatly appreciated !!
View 14 Replies
View Related
Aug 18, 2004
I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.
Thats the dream.
The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.
Here is what I have as of this moment for my sp.
ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/
PRINT @dtPP
RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/
The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '
Any help would be greatly appreciated as I am about to kick someone/something.
Thanks
View 2 Replies
View Related
Apr 27, 2005
Rather than the real code, here's a sample we came up with.
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.
View 2 Replies
View Related
Nov 5, 2015
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES
View 9 Replies
View Related
Mar 17, 2008
I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:
Code Snippet
Months
ProductGroup Article Year 1 2 3 4 5 ....
Bicycles 1020 2007 1500 2000 etc.
2008 3000 3000
Var. abs. 1500 1000
Var. % 100% 50%
1025 2007 0 1000
2008 500 1200
Var. abs. 500 200
Var. % 500% 20%
Motorcycles etc.
View 14 Replies
View Related
Mar 21, 2007
in footer of my report i show a current date by this
=Format(Today(), "d")
as a result i gave those
3/21/2007
but wont date like this 03/21/2007
How ?
View 1 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Nov 29, 2006
i have a table full of cars, from many makes and years (along with other info).
but I DONT want 2005 Ferrari's, or 2006 Ferrari's.
tried making a SQL statement, but it seems to filter out ALL ferrari's.
Here is the SQL i've tried:
SQL Code:
Original
- SQL Code
--SQL Stm #1
select COUNT(*)
from items
where account_id in (1667) and
items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND
( (items.Make != 'Ferrari' AND items.Year != '2005')
OR (items.Make != 'Ferrari' AND items.Year != '2006') )
--SQL Stm #1
SELECT COUNT(*) from items
where account_id=1667 AND
items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND
(items.make!='ferrari' and (items.year!=2005 OR items.year!=2006))
--SQL Stm #1SELECT COUNT(*) FROM items WHERE account_id IN (1667) AND items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND ( (items.Make != 'Ferrari' AND items.Year != '2005') OR (items.Make != 'Ferrari' AND items.Year != '2006') ) --SQL Stm #1SELECT COUNT(*) FROM items WHERE account_id=1667 AND items.ad_status IN (3,4) AND items.vehicle_type = 'passenger' AND(items.make!='ferrari' AND (items.year!=2005 OR items.year!=2006))
both takes out all ferrari's ?
View 3 Replies
View Related
Oct 12, 2007
Hi all -
We are planning to use a 2-node SQL Server 2005 cluster as part of a new Biztalk 2006 project, and I would like some advice regarding SQL Server service packs and editions.
Testing has already been running for some time without problems, but the test environment (a) is not clustered, (b) is using SQL Server 2005 Standard Edition rather than Enterprise and (c) has neither of the SQL Server service packs installed.
As this is a major project for us, my instinct would be to use Enterprise Edition (maximise performance and scalability)
and to apply SP2 (because I thought it was best practice to be up-to-date). I would have liked to do this before we go Live so we don't run the risk of having to change afterwards if we hit a problem.
Understandably, the project guys' perspective is this: testing so far has not been affected by SQL Server bugs, and we should go Live with the environment we have tested in, albeit with the planned 2-node cluster which is seen as a 'must have'.
I have trawled Books Online, Technet and other resources without success, so any thoughts or info on required, recommended or preferred combinations of Biztalk 2006 with SQL Server 2005 editions and service packs in a clustered
environment would be greatly appreciated - thanks!
Michael
View 5 Replies
View Related
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
Sep 7, 2013
A#B#C
A#B
A#B#C#D
How do I split above value in columns by "#"?
I need output:
Column1 Column2 Column3 Column4
A B C
A B
A B C D
View 4 Replies
View Related
Mar 30, 2015
I want to display full name column as FirstName and LastName.
Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav
i.e. Before Space is FirstName and After Space is LastName.
View 9 Replies
View Related
Jul 23, 2005
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
View 6 Replies
View Related
Feb 6, 2008
I have a table called products with the values like
ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J
I need to display each productid's with
ProductId ProductName
10 A
20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J
I will be appreciated if you can send me the code.
Thanks,
Mears
View 5 Replies
View Related
Feb 6, 2008
I have a table called products with the values like
ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J
I need to display each productid's with
ProductId ProductName
10 A
20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J
I will be appreciated if you can send me the code.
Thanks,
Mears
View 10 Replies
View Related
Nov 14, 2007
I am trying to get an address field into 2 colums. I need the number value in one column and street name in another column.
The data is stored:
876 blue ct
9987 red dr
23 windyknoll
This is what I haveelect
substring(Address,0,charindex('',Address)) as number
,substring(Address, (charindex('',Address)+1)
,len(Address)) as address
from contact
View 7 Replies
View Related
Jan 22, 2008
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that.
I have a table like this:
Select Name from Cars;
Result:
Col1
BMWMercedesFordAudi
But i like to make a query so it is displayed like this:
Col1 Col2
BMW FordMercedes Audi
So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View 1 Replies
View Related
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
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
Feb 21, 2008
hi,
i have labels for data stored in one cell
eg: item1; item22; item231;
and i want to convert it in following output
(probably using substring and charindex)
No_question| item_position | label
1|1| item1
1|2| item22
1|3| item231
any idea?
View 2 Replies
View Related
Feb 7, 2006
Hello,
Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row?
As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables.
How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row.
Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts.
Thoughts?
Thanks in advance,
Dan
View 5 Replies
View Related
Aug 19, 2004
I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help.
Thank you all! Timpy
View 1 Replies
View Related
Jan 13, 2005
Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive
FName
John?Doe
FName LName
John Doe
thanks for the help
prit
View 7 Replies
View Related
Jan 7, 2015
I've a table similar to the one below, with a SKU, Category and Cost, and need using a simple select command, split the cost in two columns one for each category (1,2), I used a self-join, and it works, but it doesn't show values not equal in both categories
Declare @Tmp_SKUCatValue Table(
SKU char(7)
,Cetegory Int
,Unit_cost Decimal
);
INSERT INTO @Tmp_SKUCatValue (SKU, Cetegory,Unit_cost)
Values
('sku-001',1,120)
[code].....
The result is as
SKU----------UCost_Cat1-----UCost_Cat2
sku-001------120--------------222
sku-002------126--------------228
sku-003------132--------------234
sku-004------138--------------240
sku-005------144--------------246
-----------------------------------------------------------
but missing the following lines,
SKU----------UCost_Cat1-----UCost_Cat2
sku-006------333--------------null
sku-007------null--------------444
Is ok to not show sku-008 as it is not part of category 1 or 2?
View 2 Replies
View Related
Jun 12, 2014
I've a table as below
custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7
How to split csv values to new fields. so that the desired output should be as below
custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7
View 9 Replies
View Related
Mar 6, 2015
I have a ipaddress column is there where i need to split the column into two columns because of values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throuh some error.
select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
View 16 Replies
View Related
Feb 16, 2008
Hi,
I have data like this in my table:
AppId Gender
1 x
2 y
3 x, y
4 x, y, z
I need to transform like this:
AppID Gender
1 x
2 y
3 x
3 y
4 x
4 y
4 z
How to do this?
Thanks in advance
View 10 Replies
View Related
Jan 23, 2008
HI
Please help I didn't get right solution for this problem. So I am posting again with full details.
Scenario
col1---col2------col3----col4-----col5------col5
123-----AB--------WE-----Name------Add------Prod1
123-----AB--------DC-----Name------Add------Pro512
123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78
How do I select Distinct row by col1 from the above scenario. Expected result will be
123-----AB--------WE-----Name------Add------Prod1
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78
OR
123-----AB--------DC-----Name------Add------Pro512
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78
OR
123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78
Please Help.
View 12 Replies
View Related
Mar 6, 2014
I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.
Is there a way I can extract the values from the string into two separate columns?
XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c
View 5 Replies
View Related
Mar 6, 2015
I have a ipaddress column is there where i need to split the column into two columns because of
values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throw some error.
select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
View 1 Replies
View Related
