How Do I Get Two Sets Of Rows In A Single Output?

Apr 11, 2008

Hello,

I want two different set of rows in a single output. For example - the query gets records from the same tables, but first condition is a date range of 60 days and value = '1'
then the second condition is a date range of 180 days and value = '2'

Is it possible?

Thanks

View 6 Replies


ADVERTISEMENT

Need Help On GROUPING Sets Of Rows And Comparison

Jan 24, 2007

Hi folks. Hope all the gurus including Brett,Pat Phalen, RdjBarov, r937 are fine. ;)
been so long to ask stupid question. Here's the question and i really need help on this.

i have data that tracks patterns of bus stops from one point to another.
like point a, to point b, point b to point c forms one pattern.
point a to point c , point c to point b should be a different pattern.


create table #journeypatterns (patternid int ,points varchar(100))
go
insert #journeypatterns
select 1 ,'a' union all select 1 ,'b' union all select 1,'c'
union all select 2,'a' union all select 2,'c' union all select 2,'b'
union all select 3 ,'a' union all select 3 ,'b' union all select 3,'c'


select * from #journeypatterns


patternid points
1 a
1 b
1 c
2 a
2 c
2 b
3 a
3 b
3 c


what i want is to get unique pattern value of sequence of points by grouping on patternid. if the sequence of points change, i need a unique value against that pattern.
like for patternid 1, sequence of points a,bc for example should be abc.
for patternid 2, sequence of points a,c,b for example should be acb.
again patternid 3, sequence of points a,bc for example should be abc.

i tried CHECKSUM_AGG which brutally failed in production because the checksum values for each single point when summed produce SAME result for different patterns.

select checksum_agg(binary_checksum(points)) ,patternid
from #journeypatterns
group by patternid

961
962
963

here patternid 2 should be different because sequence is acb. i know checksum is not the right approach for what i need.

I NEED A GENERIC FUNCTION, that marks the pattern differences, my ultimate goal was to create a procedure, whom a patternid should be passed, and it would result the NEXT patternid in the table which has the SAME ORDER OF point sequences.

now folks, i can do this holding all data into a temp table and write a cursor to traverse through each patternid and concatenate the sequence of points.
BUT, using this approach is the ugliest, as it has slow down the process badly and boss is not happy with the performance. the table holds a lot of data.
I NEED a query rather than a cursor on the fly to resolve this.
Here's the query i am using to get the current sequence of a pattern and then i have to search all sequences similarly against it.

declare @patternid int
set @patternid =1
declare @sequence [varchar] (100)
declare @id varchar(10)
declare cr_sequence cursor fast_forward for select points from #journeypatterns where patternid=@patternid
open cr_sequence
fetch next from cr_sequence into @id
while @@fetch_status = 0
begin
select @sequence = isnull(@sequence,'')+@id
fetch next from cr_sequence into @id
end
print @sequence -- next i have code to find the similar sequence for another patternid.... which is not mentioned here but is similar

View 5 Replies View Related

Find Matching Sets Of Rows

Apr 11, 2006

Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a

View 7 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Limiting Rows Returned By Result Sets

May 20, 2008

In MySQL we use "SELECT (....) LIMIT 0, 10" to only return the first 0 to 10 records. Alternatively we could do "LIMIT 10, 20" to return the 10th to 20th records.

What's the equivilent of this in SQL Server?

Thanks

View 4 Replies View Related

SQL Server 2012 :: Identify Sets Of Rows

Jul 15, 2015

I have the following sets of records:

ColA ColB
----- -----
21 A
22 A
23 A
24 B
25 B
26 D

What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:

ColA ColB ColC
----- ----- -----
21 A 1
22 A 1
23 A 1
24 B 2
25 B 2
26 D 3

I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.

View 4 Replies View Related

Query That Get Sets Of 3 Rows Based On Given Date?

Aug 16, 2014

I need to get 3 rows per set based on a date given (must be this date) but I want the rows to be based on this date as...

1 ( row where the date from the date column is the next date after given date )

0 ( row where the date is the closest date prior to the date given )

-1 ( prior to the date at 0 )

And add a column with the relative number.

** The dates for the same name and item will never repeat.

For example, a set of rows...

Row ID, Name, Item, Number, Date
1 Andy, Item1, 12030, 2014-06-30
2 Andy, Item1, 62030, 2014-03-31
3 Andy, Item1, 30300, 2013-12-31
4 Andy, Item1, 40030, 2013-10-31
5 Andy, Item1, 50030, 2013-08-30
6 John, Item2, 50240, 2014-04-30
7 John, Item2, 41400, 2014-03-31
8 John, Item2, 40509, 2014-01-31
9 Andy, Item2, 24004, 2014-03-31
10 Andy, Item2, 20144, 2013-12-31
11 Andy, Item2, 20450, 2013-09-30
12 Andy, Item2, 25515, 2013-06-30

If I have 2014-03-15 as the date and search for 'Andy', I expect...

Row ID, Item, Date, Relative Date
2, Item1, 2014-03-31, 1
3, Item1, 2013-12-31, 0
4, Item1, 2013-10-31, -1
9, Item2, 2014-03-31, 1
10, Item2, 2013-12-31, 0
11, Item2, 2013-09-30, -1

This is what I'm using which I have no issues switching if necessary...

DATEDIFF( quarter, 2014-03-31, date )
date BETWEEN DATEADD( quarter, -1, '20140315' ) AND
DATEADD( day, 1 ( DATEADD ( quarter, 2, '20140315' ) )

which returns...

Row ID, Item, Date, Relative Date
2, Item1, 2014-06-30, 1
3, Item1, 2014-03-31, 0
4, Item1, 2013-12-31, -1
9, Item2, 2014-03-31, 0
10, Item2, 2013-12-31, -1

Not sure if date math is the best option here. Perhaps using row_number() in some way?

I'm trying to avoid having to process the entire table programmatically given the size of the data set.

View 8 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Single Row Output

Apr 24, 2007

Hi
i hav a query as

select skill_id from o_skills where employee_id=78

Output of the above query is:

skill_id
-----------
5
13
3

But i want output as


skill_id
--------
5,13,3


how can i get the above result
Thanks in Advance


Malathi Rao

View 4 Replies View Related

SQL Server 2014 :: Need Output In Single Row

Oct 29, 2015

I need out put in single row for below 2 queries using joins

SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],
CPU_Count AS NumberofCPU
FROM [master].[sys].[dm_os_sys_memory]
CROSS JOIN sys.dm_os_sys_info

[code]....

View 4 Replies View Related

How To Merge Report Output Into Single File?

Mar 14, 2008

Hi

I have read a few other posts about how to merge multiple report output files into a single document e.g. a single pdf.
There are a few approaches:
1) Generate post script files and then merge and oprint via a post script driver.
2) Generate seperate pdf files and then merge them into single document using a custom library.

I have a third idea and would appreciate any input:

Dynamically generate a RDL file that contains sub-reports, one for each report required in the final document, publish and run this as the final report. This could happen way before actually running the report i.e. the user has a tool where they select reports for a pack, tool then generates new RDL file and publishes it to sql reporting services, gets run at some later point in time.

Some challenges:
-Generate a table of contents with page numbering?
-Layout of sub-reports, not sure how they would be rendered across multiple pages?
-Managing parametes across sub-reports at run time.

Any suggestions or ideas?

Thanks

T

View 1 Replies View Related

Merging Two Rows Into A Single One

Mar 5, 2012

I'm using a shipping program called endicia professional that allows for database manipulation to make my processing easier. I've managed to fix the database here and there but have had an issue combining orders from a single customer when theybuy more than one item. Ideally I would like to have it combine rows when a customer purchases items going to the same address. To avoid having an issue where the address line is the same ie two people live in the same appt complex and it combines these I thought we could use qualifiers as the purchase will have name, order Id that should be unique enough

Order-id name address sku
1234 John 46 easy ln. A27
1234 John 46 easy ln. B32

Results:
Order-id name address sku
1234 John 46 easy ln. A27,b32

View 6 Replies View Related

How To Get All Same Rows In Single Line

Mar 6, 2013

i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated .my query is :

BEGIN TRY
SET NOCOUNT ON
DECLARE @l_numberOfRecordsInserted INT
-- Check if valid Application User ID is passed
IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )

[code]....

so last column section name should come as comma seperated .

View 3 Replies View Related

Single Row Into Multiple Rows

May 8, 2008

Hi All,

We've a table as in the following format:













PK_Column1
PK_Column2
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1
Issue_Date2
Issue_Amount2
Issue_Category2
Issue_Reject2


We need to divide it into two new tables as follows:








UniqueID
PK_Column1
PK_Column2
And











UniqueID
PK_Column1
PK_Column2
Sequence_ID
Issue_Date
Issue_Amount
Issue_Category
Issue_Reject

Unique1


1
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1

Unique2


2
Issue_Date2
Issue_Amount2
Issue_Category2


Unique3


1
xx


xx

Unique4


2
xx

xx


Unique5


3
xx







4

xx



There will be one UniqueID for each row.
We'll get the uniqueID and PK1 and PK2 in a file.
Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table.

Can we do this without using cursors?
This is going to be one time process.

Any ideas are appreciated.

Thanks,
Siva.







View 1 Replies View Related

Getting Multiple Rows In A Single Row

Jan 21, 2008

Hi,

I've a temp variable where I'm moving some columns like below:







id
value
type1
type2

0
ab
type1val1
type2val1

0
cd
type1val1
type2val1

0
ef
type1val1
type2val1

1
ab
type1val2
type2val2

1
cd
type1val2
type2val2

1
ef
type1val2
type2val2
What I want to do is group these by their id and get the following o/p
ab,cd,ef type1val1 type2val1
ab,cd,ef type1val2 type2val2

The grouped values need to be separated by commas.

What I'm doing currently:
I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p.

Can anybody help me out?

Thanks,
Subha

View 4 Replies View Related

Spliting Single Row Into Multiple Rows

Sep 20, 2006

I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows.
My table structure is:
create table #scoresheet
(Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255),
Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255),
Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255));
I would like to have three rows with four columns.
What I've done so far is create a stored procedure that uses a table variable:
create procedure sp_splitsinglerow as

declare @Scoresheet_rows_table_var table (
ReviewArea varchar(25),
NumericalValue int,
VI varchar(10),
Comments nvarchar(255));
insert into @Scoresheet_rows_table_var
(ReviewArea, NumericalValue, VI, Comments)
select Decisions, DNumericalValue, DVI, DComments
from #scoresheet

The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.

Any ideas on how to achieve that?

BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)

View 2 Replies View Related

Split Single Row To Multiple Rows

Feb 27, 2012

I have a table which looks like

Low High
-------------------------
cx01 cx04
sn05 sn08

I need output like

Result
-------------
cx01
cx02
cx03
cx04
sn05
sn06
sn07
sn08

How to get this output using sql query?

View 8 Replies View Related

T-SQL (SS2K8) :: Multiple Rows Into A Single Row

Nov 7, 2014

I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.

But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.

This is a small example of what the rows look like in the resultset, and what I want to single to be.

DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int

[Code] ....

I am not sure how to write the query to have all the data in a single row.

View 2 Replies View Related

Display 20 Rows At Single Time

Sep 25, 2015

The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company rules and regulations.Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.

The result set changes as per the start date and end date which i recieve as parameters. In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.

Current Code :
Step 1 ) Get the required result set in temp table .
Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment)
Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated.
Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.

View 9 Replies View Related

Return Multiple Rows As A Single Row

Oct 8, 2014

I’m trying to return data in a single row.Here’s what my table looks like:

Employee #,Hours Type,Total Hours
1234,Regular,40
1234,OT,8
1234,Regular,36

[code]....

I need the results of my query to total each hours type and group together:

EmpNo,Sum Of Regular, Sum of Overtime,Sum of Doubletime
1234,76,19,12
7777,45,8,5

I don’t know how to get the data returned in a single row.

View 2 Replies View Related

Multiple Rows Into A Single Field

Oct 12, 2005

Hi
I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..

CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner
(
@Crime_No varchar(20),
@Unit_ID int
)
as
begin
DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int
DECLARE @Defect_ID varchar(100)

select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID

SELECT
TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1,
dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id,
dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone,
dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,
TBL_TR_ACCIDENT_VEHICLE.Registration_Number,
TBL_TR_ACCIDENT_VEHICLE.Crime_No,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre,
TBL_TR_ACCIDENT_VEHICLE.vehicle_Make,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model,
TBL_TR_ACCIDENT_VEHICLE.Unit_ID,
TBL_TR_ACCIDENT_VEHICLE.RowID,
TBL_TR_ACCIDENT_VEHICLE.UserID,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color,
TBL_TR_ACCIDENT_VEHICLE.HP,
TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity,
TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle,
TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight,
TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight,
TBL_TR_ACCIDENT_VEHICLE.Skid_Length,

(select TBL_TR_Person_OutsideDetails.OutSide_state from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],

(select TBL_TR_Person_OutsideDetails.OutSide_City from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]


---here I faced the problem-
/*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/

(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5))
FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE
WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number)
select @Defect_ID



FROM
tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE
where
tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
AND
TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No
AND
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner

end
GO

View 2 Replies View Related

How To Display Many Rows Data To A Single Row.

Mar 14, 2008

I have a table with only one column as

Emp_Name
A
B
C
D
E

I want to display this data as

Emp_Name
A, B, C, D, E

Is this possible? I am using SQL server 2005.
Any help is appreciated.

Regards,
Vinesh

View 2 Replies View Related

Transact SQL :: Merging Every 3 Rows Into A Single Row

Aug 18, 2015

I have 2 columns (ID, Msg_text) in a table where i need to combine every 3 rows into single row. What would be the best option i have? I know by using 'STUFF' and 'XML PATH' i can convert all the rows into a single row but here i'm looking for every 3 rows into a single row.

View 3 Replies View Related

How To Merge Multiple Rows Into Single Row

May 13, 2008

Hi,

I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3

NewsDetails
Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2

Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW

Thank you.

View 2 Replies View Related

Splitting Single Row Of Table In To Two Rows

May 24, 2007



Hi All,



I was wondering is there any way by which i can split a single row in my table into two rows in my table in design of my report. In my table there are 16 rows i cant design table with 16 rows as it will increase size of my report body & it will give problem while exporting. Instead what i want to do is , design a table with two header rows first header with first 8 columns & second with remaining 8 columns.



The table should look like this



//column 1 2



ID NAME

AGE SEX

-----------------------------------------------------------------------------------

2 abc

10 M

-----------------------------------------------------------------

3 def

20 M





Is it possible



Thanks

-- Darshan

View 4 Replies View Related

How Can You Get Multiple Output Rows From One Row?

May 5, 2003

I inherited a database with Street, CrossStreet1 and CrossStreet2. I wish to read the row once and output 3 rows.

I know I can run 3 separate query's but why read through 1 million rows 3 times, if you can read through it once.

I can do this in SAS using the output statement.
Hoping someone knows how to do this in SQL.
:confused:

View 6 Replies View Related

How To Display Multiple Rows Of A Table In Single Row

Dec 15, 2007

DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2 

View 1 Replies View Related

Deleting Duplicate Rows Within A Single Table

May 14, 2004

I was wondering if anyone had a suggestion as to how to delete duplicate rows from a table. I have been doing this:

SELECT * INTO TempUsersNoRepeats
FROM TempUsers2
UNION
SELECT * FROM TempUsers3

This way I end up with a total of four tables (the fourth table being the original Users table) and I was hoping that there was a way that I could do this all within the the original Users table and not have to create the three TempUsers tables.

Thanks,
Ron

View 5 Replies View Related

Adding A Single Change To All Rows In A Column

Jan 16, 2002

View 1 Replies View Related

Adding A Single Change To All Rows In A Column

Jan 16, 2002

We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?

View 2 Replies View Related

SQL View To Split Rows In Single Table...

May 24, 2007

I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for

I have a table is MSSQL 2000 that looks as follows:


Code:



id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1



Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:


Code:



custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1



Any help would be great! I imagine some sort of join command can be constructed using a single table?

View 2 Replies View Related

Update Multiple Rows In A Single Query?

Jul 30, 2007

I know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order.



Code:


update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111




PHP Code:




 $qt="update categories set ";
for($i=0;$i<$num;$i++){
    $a=$i+1;
    $qt.="sortorder=$i";
    if($a<$num){
        $qt.=", ";
    }
}
$qt.=" where userid=111"; 





Using PHP I can amend the loop above to slot in a row I want so I can change the sort order.

unfortunately I'm not sure how to build such a query in mssql, can anyone help?

View 5 Replies View Related

Return Multiple Rows Into One Single String

Nov 1, 2005

Hi,

I need to return multiple rows into one single string

Declare @String varchar(1000)

Create table Cus (CusId Int,CusName varchar(10))

Insert into Cus Select 1,'John'
Union All
Select 2,'Bob'

Select * from Cus returns

1John
2 Bob

I need to return the all the rows from Cus table into a single string. The return is dynamic.
I do not know the number of rows returned

My result should be

@String = 1,John,2,Bob

How can i do that ?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved