|
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
|
|
|
|
|
Perfect. Thanks very much. I knew I needed some grouping to happen and couldn't think of putting some function like max together. It works exactly as I needed it to.
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]
|
|
|
|
|
 you are welcome
|
|
|
|
|
Hi,
I have one column in one table in sql server like
SUK
-----
abc 111 bbb 1212
pqr 222 ccc 2323
i have to split this in to 4 columns
how to do this,,,please help
DOnt update the table ,only for viewing. in a storedprocedure i want this
Rakesh
|
|
|
|
|
SUK is a good name for that column - as in it SUKs having an awful structure. If you needed those values as discrete columns they should have been discrete to start with!
Anyway, a partial solution for you; if you can guarantee the startindex and length of each part it's not too bad:
;WITH SukTable ([Suk]) AS
(
SELECT 'abc 111 bbb 1212'
UNION SELECT 'pqr 222 ccc 2323'
)
SELECT
SUBSTRING(SUK,1,3) AS Field1,
SUBSTRING(SUK,5,3) AS Field2,
SUBSTRING(SUK,9,3) AS Field3,
SUBSTRING(SUK,12,5) AS Field4
FROM SukTable
Otherwise, assuming the parts are separated by a single space, its possible but becomes a dog very quickly - here's a solution for the first 2 parts, already becoming unweildly:
;WITH SukTable ([Suk]) AS
(
SELECT 'abc 111 bbb 1212'
UNION SELECT 'pqr 222 ccc 2323'
)
SELECT
LEFT(SUK,CHARINDEX(' ',Suk,0)-1) AS Field1,
SUBSTRING(SUK,CHARINDEX(' ',Suk,0),CHARINDEX(' ',Suk,CHARINDEX(' ',Suk,0))-CHARINDEX(' ',Suk,0)) AS Field2
FROM SukTable
Edit: Another option is something like this: http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql[^]
Fix your data model, before it's too late.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
