|
I have a db with following values :
col1 col2 col3 position
aaa rack1 1-3 3
bbb rack2 22-23 2
ccc rack3 19-20 2
Output should be
col1 col2 col3 position
aaa rack1 1-3 1
aaa rack1 1-3 2
aaa rack1 1-3 3
bbb rack2 22-23 22
bbb rack2 22-23 23
ccc rack3 19-20 19
ccc rack3 19-20 20
Basically, its taking the position and creating a row for each value from col3.
Can i do this on-fly and create a temp table? or i have to create a procedure?
thanks!!!
VK
|
|
|
|
|
If you are doing this in the database you are going to have to write some code, you can't magic it into the multiple row. I would look at the method of inserting the original record and change or enhance that method.
Alternatively you can use an insert trigger spit, I would do this as a last resort.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
CREATE TABLE #Temp
(
ID INT IDENTITY(1,1),
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)
CREATE TABLE #Temp2
(
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)
INSERT INTO #Temp
SELECT 'aaa', 'rack1', '1-3', '3' UNION
SELECT 'bbb', 'rack2', '22-23', '2' UNION
SELECT 'ccc', 'rack3', '19-20', '2'
DECLARE @TotalRowCount INT
DECLARE @RowCount INT
DECLARE @Positon INT
DECLARE @I INT
SET @RowCount = 1
SELECT @TotalRowCount = COUNT(*) FROM #Temp
WHILE @RowCount <= @TotalRowCount
BEGIN
SELECT @Positon = Position FROM #Temp WHERE Id = @RowCount
SET @I = 1
WHILE @I <= @Positon
BEGIN
INSERT INTO #Temp2
SELECT Col1, Col2, Col3, (SUBSTRING(Col3,0,CHARINDEX('-',Col3)) + @I - 1) FROM #Temp WHERE Id = @RowCount
SET @I = @I + 1
END
SET @RowCount = @RowCount + 1
END
SELECT * FROM #Temp2
DROP TABLE #Temp
DROP TABLE #Temp2
|
|
|
|
|
hiiiiiii, I have to make a lan chatting software for windows using ADO.NET & sqlserver.The criteria is that what we enter in the chatbox should update in database & the other lan user see it & also who is online on lan.plz help & provide code
|
|
|
|
|
Member 9131839 wrote: plz help & provide code
Sorry but that is not how this forum works. However, if you try a search of the Articles[^] you will find lots of examples of chat programs and database usage that will help in developing your solution.
|
|
|
|
|
Member 9131839 wrote: I have to make
Read that part again; it conflicts with the last part of your post.
Bastard Programmer from Hell 
|
|
|
|
|
Member 9131839 wrote: provide code
No. People are not going to just give you their code they worked hard for. You have to work on your own project and when you run into trouble with a part of it, then ask for help.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
I am getting the above error on MySQL stored procedure..
Kindly help...
here is the error followed by the code:
1064 - You have and error in your SQL syntax. check the manual that corresponds to your MySQL server version for the right syntax to use near '
END
ELSE
BEGIN
INSERT INTO salary_slips (salary_slip_month, salary_slip_y'
at line 13
BEGIN
SET param_slip_released = FALSE;
IF EXISTS (SELECT salary_slip_id FROM salary_slips WHERE salary_slip_month = param_slip_month AND salary_slip_year = param_slip_year) THEN
BEGIN
SET param_slip_released = (SELECT salary_slip_released FROM salary_slips WHERE salary_slip_month = param_slip_month AND salary_slip_year = param_slip_year);
IF param_slip_released = FALSE THEN
BEGIN
SELECT * FROM salary_slips;
END
END
ELSE
BEGIN
INSERT INTO salary_slips (salary_slip_month, salary_slip_year) VALUES (param_slip_month, param_slip_year);
END
END;
|
|
|
|
|
jrahma wrote: VALUES (<layer>param_slip_month, param_slip_year)
I cannot see where you set the values of these parameters!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a list to populate. I have a query to populate it in mssql 2008. It involves three tables. If the query returns the value then I populate it and it is done. But , if the query doesnot return anything then I want to run another select statement because I do not want to leave the list view empty. I have to populate at least the names of the client. It could be something simple but I am just beginning to use mssql so I am stuck.
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
if does not return anything,
I want to run another sql to atleast get the name of the client even if there is no related detail with that client.
second sql would be
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= '100'
[P.S. the bID field of Table A could actually either be bID as is or C.clientID inserted into bID field of Table C.]
I am confident about the queries but its just about extracting the values from the result.
Thanks,
|
|
|
|
|
Look at @@RowCount[^]
Remember that it changes with the next executed statement.
|
|
|
|
|
I don't understand the LEFT JOIN between A.bID and C.clientID. You infer that the second query will always get the answer you are looking for so, an INNER JOIN is appropriate. With further examiniation, only B.Detail may be NULL if you have a valid aID. It is usually easier to let the client handle the NULL column than two types of data structures.
The query below will always have data in Client. Detail may or may not be NULL. This is assuming that aID is valid.
SELECT
C.name AS Client,
B.Detail AS Detail
FROM
tableC C
INNER JOIN
tableA A
ON (C.clientID = A.bID)
LEFT JOIN
tableB B
ON (C.clientID = B.clientID)
WHERE
aID = '100'
|
|
|
|
|
You can use the Case Statement for this:
Declare a variable @Client Varchar(100) = NULL, now put
Select C.name as Client , @Client = C.name,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
Now check in Case like this::
CASE WHEN @Client IS NULL THEN
... Put Your Another Select Query Here ...
END
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Hi There
You can do a rowcount and if a count on the no of rows is zero, then Select .....
For example
IF EXISTS (SELECT COUNT(*) FROM tablename)
BEGIN
SELECT ..... FROM TABLENAME
END
-- if no data then do... that is if row count is zero
ELSE
SELECT ..... FROM TABLENAME
|
|
|
|
|
Another method is to use a variable to hold the number of records and based on the number of records, decide which sql statement to execute.
For example
--PUR ROW COUNT IN A VARIABLE
DECLARE @rowcount INT
SELECT @rowcount = COUNT(*) FROM tablename
IF @rowcount >= 27 --SPECIFY the number expected
BEGIN
PRINT 'TEST-1' --OR SELECT STATEMENT
END
ELSE
BEGIN
PRINT 'TEST-2' -- OR SELECT STATEMENT
END
hope this helps.
Cheers
|
|
|
|
|
TRY THIS
Declare @rowcount int
select @rowcount = (Select count(*) from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= 100)
if @rowcount > 1
begin
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= 100
else
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= 100
end
Hope this will help 
|
|
|
|
|
I have one SQL Table with 2 columns as below
Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL
Now There are 2 Records in Table
1-1-2012, 5
1-3-2012, 7
Output of Result should be as below if i give date range StartDate as 1-1-2012 and EndDate as 1-15-2012
1-1-2012 5
1-2-2012 0
1-3-2012 7
1-4-2012 0
1-5-2012 0
1-6-2012 0
.
.
.
1-15-2012 0
Means Query should return all the dates of given range with Quantity and if no entry in Table then 0 for Quantity.
How to Do it? Please suggest with Query
|
|
|
|
|
You could accomplish this with a script rather than a single query. I have not run this and if you are allergic to table vars then stop reading. Presuming you have a begin date that is less than or equal to the end date you can proceed like this...
declare @loopDate as datetime
declare @dateRange as table ( aDate datetime )
set @loopDate = @beginDate
insert into @dateRange (aDate) values (@loopDate )
while @loopDate < @endDate
begin
Dateadd(day, @loopDate, 1)
insert into @dateRange (aDate) values (@loopDate )
end
select aDate, isnull(quantity, 0)
from
yourTable a
left outer join @dateRange b on
b.aDate = a.Productiondate
I have not tested this. There might be a syntax flaw. Flame away. - Bill O'Brien
|
|
|
|
|
Usually someone will say use a generated (permanent) table, however your solution seems logical.
P.S. I have not tested it either. I hope it will work for rahulpatel112.
|
|
|
|
|
It is amazing what you can find in these forums if you look. Bob has got it right and if you had even looked just a little further you would have found this answer[^] 4 threads down!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you need it to be a query and not a Stored Procedure you can try this:
WITH DateRange(Date) AS
(
SELECT @StartDate Date
UNION ALL
SELECT DATEADD(day, 1, Date) Date
FROM DateRange
WHERE Date < @EndDate
)
SELECT dr.date
,case mt.quantity when null then 0 else mt.quantity end quantity
FROM DateRange dr
LEFT OUTER JOIN MyTable mt
ON dr.date = mt.productiondate I haven't tested it, but you'll get the idea.
|
|
|
|
|
DECLARE @TargetDate SMALLDATETIME
DECLARE @InitialDate SMALLDATETIME
SET @InitialDate = '06-19-2012'
SET @TargetDate = '06-25-2012'
WHILE @InitialDate <= @TargetDate
BEGIN
PRINT @InitialDate
SELECT @InitialDate = DATEADD( D, 1, @InitialDate )
END
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
i need to make a duplicate copy of an existing database with a new name in sql server and this has to be done using QUERY ONLY,
i tried to take a backup and restore but that restores with the same name...
|
|
|
|
|
|
thank you that worked for an extent, but still finding a bit of problem when trying to restore the database from sql query using c# in asp .net
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
