How To Display Multiple Rows Of A Table In Single Row

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 + ',', '') + desig
FROM emp
SELECT eid=@emp1,ename = @emp,desig=@emp2

 



ADVERTISEMENT

SQL 2000: Inserting Multiple Rows Into A Single Table

To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you

View Replies View Related

SQL 2000 How To Insert Multiple Rows Ina Single Table

 

I am using SQL 2000
I am getting a syntax error when I parse this sql script:
 
insert into Elec_Sub_Test1
values ('10-20-2007',35),
          ('10-21-2007',24)
 
What is the correct syntax to insert mutlipe rows in a single table.

View Replies View Related

Copying Rows From Multiple Tables To A Single Table

 

Hi,
 
I have 3 tables with the follwing schema
Table <Category>
{

UniqueID,
LastDate DateTime
}

 
Assume the follwing tables with data following the above schema
 
Table Cat1
{

1, D1
2, D2
3, D3
}
Table Cat2
{

2, D4
3,D5
4, D6
}
Table Cat3
{

1, D7
3,D8
5,D9
}
 
I have a Master and the schema is as follows
Table master
{

UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
 
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{

UniqueId   cat1      cat2     Cat3
------------   ---------   -------     -----------
1            D1       NULL     D7
2            D2        D4      NULL
3            D3        D5        D8
4          NULL       D6      NULL
5           NULL      NULL     D9
}
 

Please remember the column names will be same as that of table names
 
can any one pelase let me know the query t o acheive this
 
Thanks for your quick response
~Mohan Babu

View Replies View Related

How To Display Many Rows Data To A Single Row.

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 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

 

Please can anyone help me for the following?
 
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
 
for eg:
data

Date           Shift  Reading
01-MAR-08     1     879.880
01-MAR-08     2     854.858
01-MAR-08     3     833.836
02-MAR-08     1     809.810
02-MAR-08     2     785.784
02-MAR-08     3     761.760
 
i want output for the above as:

Date              Shift1         Shift2         Shift3
01-MAR-08     879.880       854.858       833.836
02-MAR-08     809.810       785.784       761.760
 Please help me.

View Replies View Related

Single Row Into Multiple Rows

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 Replies View Related

Getting Multiple Rows In A Single Row

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 Replies View Related

Spliting Single Row Into Multiple Rows

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 Replies View Related

Multiple Rows Into A Single Field

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 Replies View Related

How To Merge Multiple Rows Into Single Row

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 Replies View Related

Inserting Multiple Rows With A Single INSERT INTO

Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)

View Replies View Related

Script To Combine Multiple Rows Into 1 Single Row

Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?

View Replies View Related

Script To Combine Multiple Rows Into A Single Row

Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance

View Replies View Related

Return Multiple Rows Into One Single String

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 Replies View Related

Update Multiple Rows In A Single Query?

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 Replies View Related

How To Update Multiple Rows Using Single Sql State

Hai

I want to update mutiple rows using single statement.

Gender
M
F

Now I want to update M as Male and F as Female in Gender Table using single Sql Statement.

Can anyone help me please
Thanks in Advance...

Suresh Kumar

View Replies View Related

Combine Multiple Rows Into Single SQL Record

Hello:

I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.

productID SectionID statusID
10 1 0
10 2 1
10 3 2
10 4 1
10 5 3
10 6 1
11 1 0
11 2 1
11 3 2
11 7 3
11 8 3

Need to return two rows with the respective values for each section.

productID section1 section2 section3 section4 section5 section6 section7 section8
10 0 1 2 1 3 1
11 0 1 2 3 3

Any information or if you can point me in the right direction would be appreciated.

Thanks

View Replies View Related

Generate Multiple Rows For Insert From Single Row

Dear all,

I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?

Regards,

Pedro Martins

View Replies View Related

How To Return Value From Multiple Rows In A Single String

 

I have a table having Style Nos (VarChar Col), how I can return values from multiple rows in a single string.
 
for Example if table is having 3 records :-
 
1. Style 1
2. Style 2
3. Style 3
 
It should return single value in this way
 
Style 1, Style 2, Style 3

 
 

View Replies View Related

Converting Repeating Fields In Single Row To Multiple Rows

Hi there
 
I have loaded a csv file into a table. Some fields within the file contain a varying number (up to 10) of subfields seperated by line feed characters.
 
It looks sort of like this
 
Customer No.   Payments                   Dates                       
111                    pay1|pay2|pay3          dat1|dat2|dat3               
 
I created an Unpivot transformation, and I got
 
Customer No.   Description          Detail
111                    Payments             pay1|pay2|pay3
111                    Dates                    dat1|dat2|dat3
 
After a Derived Column transformation I got
 
Customer No.   Description          Line1    Line2    Line3
111                    Payments             pay1     pay2      pay3
111                    Dates                    dat1      dat2       dat3
 
But what I really want is to end up with this:
 
Customer No.   Payments            Dates                      
111                    pay1                     dat1                        
111                    pay2                     dat2                        
111                    pay3                     dat3                 
 
Is there a transformation that will get me there, or do I just need some cunning SQL?
 
I tried to Pivot my way back to happiness but I couldn't get it to work        

View Replies View Related

Loading Data From Multiple Rows Into Single Row In Excel Sheet

 
Hi,
 
I want to load data into Excel file with following format,
 




Country

State

Total

Location


ABC

A

20

X1


30

Y1


C

100

 


XYZ

X

40

 
 
Basically I want to insert records from multiple rows into a single row; how can I achieve this using SSIS.
I am using Excel as a data source.
 
Any help is appreciated.
 
Regards,
Omkar.
 
 

View Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View Replies View Related

Report Table Does Not Display All Rows From Dataset

I have a dataset that when run returns 270 rows.  The table using the dataset in the report only prints the first row.  I have the table grouped by a status type, but this is for when I can get multi-select paramenters installed and working.  For now I just need the report to print all the returned rows.  Help!!

Thanks!

Terry

View Replies View Related

Splitting Single Row Of Table In To Two Rows

 

 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 Replies View Related

Deleting Duplicate Rows Within A Single Table

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 Replies View Related

SQL View To Split Rows In Single Table...

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 Replies View Related

Single Row Result From Mutliple Table Rows?

Hi again,

I'd appreciate advice on the following. Thanks very much!

Given this Table:
family|product|type
fam1 |abc |XX
fam1 |def |YY

What query would return this?
Family|Type1|Type2
fam1 |xx |yy


--PhB

View Replies View Related

Controlling Number Of Rows To Display In A Table And Matrix On One Page

Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?

View Replies View Related







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