How To Display Multiple Rows Of A Table In Single Row

Dec 15, 2007

declare @emp1 varchar(1024)
declare @emp2 varchar(1024)
@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


View 1 Replies


Query On Multiple Rows In Single Table?

Aug 1, 2014

I have a table named LEDGER

LEDGER has two columns named PATID and CODE

Example data:

1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092

I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.

View 5 Replies View Related

Transact SQL :: Temp Table - Display Duplicate IDs In Multiple Rows

Jun 30, 2015

I have a temp table with the following columns and data 

drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)


I use the rank function on that table and get the following results

select rank() over (order by DLR_ID,KPI_ID,BRND_ID  ) Rown,* from #temp

I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)

View 7 Replies View Related

SQL 2000: Inserting Multiple Rows Into A Single Table

Jul 20, 2005

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

Copying Rows From Multiple Tables To A Single Table

Sep 20, 2007


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

LastDate DateTime

Assume the follwing tables with data following the above schema

Table Cat1

1, D1
2, D2
3, D3
Table Cat2

2, D4
4, D6
Table Cat3

1, D7

I have a Master and the schema is as follows
Table master

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

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

SQL 2000 How To Insert Multiple Rows Ina Single Table

Oct 25, 2007

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),

What is the correct syntax to insert mutlipe rows in a single table.

View 4 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

Transact SQL :: How To Update Multiple Rows In Different Transactions In A Single Table

Jul 16, 2015

We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.

Below is the Table Structure.

Table Name       IN_USE_FG
CUST_D                     0
PROD_D                     0
GEO_D                       0
DATE_D                     0

Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package. 

Now we are getting dead lock because we are using same table at a same time. Because we are updating different records. 

View 6 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 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

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

How To Display Many Rows Data To A Single Row.

Mar 14, 2008

I have a table with only one column as


I want to display this data as

A, B, C, D, E

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


View 2 Replies View Related

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

Mar 3, 2008

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:

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

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Single Row Into Multiple Rows

May 8, 2008

Hi All,

We've a table as in the following format:


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

















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.


View 1 Replies View Related

Getting Multiple Rows In A Single Row

Jan 21, 2008


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







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?


View 4 Replies View Related

SQL 2012 :: Any Way To Display Multiple Rows Of Tabs?

Jun 20, 2013

SSMS 2012: when you open up many sql files in the IDE, it starts hiding some tabs and you have to click on the drop down at the right to navigate to the tab you want. Is there a way to make it display more than one row of tabs, so that tabs are not hidden and always displayed?

View 3 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


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

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


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

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

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
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

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.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,

(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))
select @Defect_ID

tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner


View 2 Replies View Related

How To Merge Multiple Rows Into Single Row

May 13, 2008


I have two tables of news feed NewsHeader & NewsDetails
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

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

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.


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 ";
        $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


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

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

T-SQL (SS2K8) :: Merge Multiple Rows In Single Row

Jul 8, 2015

I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.

CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical

I'm using T-SQL to generate the column type. Below is my DDL.

USE tempdb
(Prefix char(8),
Code char(5)

[Code] ....

View 7 Replies View Related

Combine Multiple Rows To Single Column

Jul 8, 2014

With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.

;WITH stored_procedures AS (
SELECT, AS proc_name, AS table_name,
ROW_NUMBER() OVER(partition by, ORDER BY, AS row
FROM sysdepends d
INNER JOIN sysobjects o ON
INNER JOIN sysobjects oo ON
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

View 6 Replies View Related

How To Update Multiple Rows Using Single Sql State

Jan 28, 2008


I want to update mutiple rows using single statement.


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

Combine Multiple Rows Into Single SQL Record

Jan 28, 2008


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.


View 4 Replies View Related

Inserting Multiple Rows With A Single INSERT INTO

Jul 23, 2005

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

Script To Combine Multiple Rows Into 1 Single Row

Dec 22, 2006

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

Script To Combine Multiple Rows Into A Single Row

Jul 20, 2005

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

Copyrights 2005-15, All rights reserved