|
Hi,
I have a table and one column is there like this
SKU Value
-----------
MKG Ex Du A918731765L
TVN Ex Du A919721354L
NCL Ex Du A901713191L
Bin Ex Du A948606458L
--- -- -- ----------
--- -- -- ----------
etc etc etc(table is having hundreds of data like this and it is a expanding table.So i have to split all of them not only the mentioned data.So a common way to split this column is required.)
I am writing a stored procedure to get this columns as
SK1 SK2 SK3 SK4 SK5
---------------------------------------------------
MKG EX Du A91873176 5L
TVN Ex Du A91972135 4L
etc etc
The values will be always in the above format.
Please help.
Thanks in advance
-- Modified Monday, October 18, 2010 12:20 AM
|
|
|
|
|
Hi,
I suggest you create a split function
CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
Then you cross apply to your data table, pivot, then manipulate the display data.
Here is an example:
declare @tmp table (data varchar(50))
insert into @tmp (data) values ('MKG Ex Du A918731765L')
insert into @tmp (data) values ('TVN Ex Du A919721354L')
insert into @tmp (data) values ('NCL Ex Du A901713191L')
insert into @tmp (data) values ('Bin Ex Du A948606458L')
select [1] as 'SK1', [2] as 'SK2', [3] as 'SK3', substring([4], 0, 10) as 'SK3', substring([4], 10, 2) as 'SK4'
from
(
select [1] , [2] , [3] , [4]
from (
select * from
@tmp t
cross apply dbo.Split(t.data,' ')
) as sourcetb
pivot
(max(val) for id in ([1],[2],[3],[4])
) as pivottable
) as t
Ryan
|
|
|
|
|
Now that is what I call sending the codz - deserves 5
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi,
I want to combine multiple rows in a single one. Have a look at the following sample. Any body know how to do this....thnx in advance
original table
------------------
FID Code Value1 Value2
1 C1 20 Null
1 C1 Null 10
2 C1 30 Null
2 C1 Null 40
To
--
FID Code Value1 Value2
1 C1 20 10
2 C1 30 40
|
|
|
|
|
SELECT FID,Code,Max(Value1),Max(Value2)
FROM TableName
Group By FID,Code
|
|
|
|
|
Perhaps you should use MIN in any of fields for Value1 or Value2
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
|
You need a pivot [^] table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how is it possible to change master Collation in SQL Server 2008.
|
|
|
|
|
If you are referring to change the ms sql server instance's collation refer this link:
http://msdn.microsoft.com/en-us/library/ms179254.aspx[^]
But remember to "detach" user defined databases as they will be dropped when you execute these steps. You can "reattach" the databases when you are done with changing the collation.
If you want to change the collation of a database alone you could use the alter database command. Check out this link:
http://msdn.microsoft.com/en-us/library/ms174269.aspx[^]
As you said master collation, I guess you mean the server instance's collation. Either ways remember to detach (in case changing the server collation) or take a backup (in case you are using alter database cmd) of user databases.
Cheers,
Karthik
|
|
|
|
|
declare @tbldate table(stdid int, sdate date, eddate date)
insert into @tbldate
values
(100, '20100102', '20100505'),
(101, '20100203', '20100302'
)
i want my result set as
check if day in date is 01 if not then make day as 01 and increment month my 1. i have the solution but thats in 2 temporary table and i want full solution in a single table.
|
|
|
|
|
You mistake is that you are treating a date as a string - lesson 1: A DATE IS NOT A STRING. - learn this lesson now and it will save you a lot of pain in the future.
You can use DATEADD within a loop. You still need to generate the sequential numbers (@day) 1-100, then you can do something like
DECLARE
@Day INT
SET @Day = 1
WHILE @Day < 101
BEGIN
SELECT DATEADD(d,@Day,GETDATE())
SET @Day = @Day+1
END
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
dear i also can do this but i by taking variable u can pass single value but i want to achieve this by query.
|
|
|
|
|
scottichrosaviakosmos wrote: achieve this by query
I don't think it can be done!
The only work around I can think of is to create a UDF (function) which returns a table. Basically move the code into a function, have it return aresult set and use the result set to join to some thing else. I have one called GenDates and use it like
Select * from Gendates(StartDate, Days)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How about "case when" ?
case when
cast(right(@dates,2) as int) > 1
then
convert(char(8),dateadd(month,1,cast(left(@dates,6) + '01' as datetime(8))),112)
else
@dates
end
|
|
|
|
|
Hello,
I have scoured the web, found MANY apps that let you do this, however I am in need of a free solution. I have product sheets in an xls file that need to be put in a database, and I cannot type a query to insert hundreds of thousands of items into a db manually.
Is there any free, easy way to insert Excel OR CSV File into MySQL OR MSSQL Database?
I appreciate any help at all, thank you! 
|
|
|
|
|
The main problem will be that Excel is unstructured data, if all your sheets are well formed and are all EXACTLY the same the you can write a program with 3 parts:
Folder scan that identifies each xls file
importer that reads each xls into a data table (either cell by cell or using Jet)
DAL to write the data table to the database.
Expecting a "free" tool to do this is a little much.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SSIS. It's free if you have SQL Server 5005 2005 Developer or above. You can do it in minutes if you have it.
Edit: Fixed typo
SG
Aham Brahmasmi!
|
|
|
|
|
For MYSQL I use the LOAD DATA SQL.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
|
|
|
|
|
I have a query of the form
select * from (
select ...
,...
,...
,a.value_1 open_value_1
,to_number(NULL) close_value_1
from table a
where a.date_col = date_1
union all
select ...
,...
,...
,to_number(NULL) open_value_1
,b.value_1 close_value_1
from table b
where b.date_col = date_2
);
If the list of columns represented by the ... is considered a primary key, for the situations where the primary keys are the same I end up with multiple rows
AA BBB CCC open_value_1
AA BBB CCC close_value_1
How would I tailor my query to give me only one row instead of two rows. Like this
AA BBB CCC open_value_1 close_value_1
Thanks for any suggestions.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Look into Pivot queries. It looks like that may be what you want.
|
|
|
|
|
|
SELECT ...,...,...,MAX(open_value_1),MAX(close_value_1) FROM
(
--YOUR SELECT QUERY
)A
GROUP BY ...,...,...
|
|
|
|
|
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
