SQL Server 2008 :: Concatenate Rows With For XML Logic

Feb 24, 2015

I am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?

This question is just for my understanding

(id INT IDENTITY(1,1),
item varchar(100))


[Code] .....

View 4 Replies


SQL Server 2008 :: How To Concatenate Column Value

Jul 29, 2015

I have a column name Classname and I would like to to Concatenate value.

ID ClassName
1 Class A
2 Class B
3 Class C
4 Class D

I need a output in ssrs report with title like (Class A, Class B, Class C, Class D) .

Can I do in SSRS as well ?

I tried join function in ssrs and I am getting #error join(Field!classname,",")

View 5 Replies View Related

SQL Server 2008 :: Concatenate Column Name With Values

Sep 17, 2015

I want to concatenate the column_names with the respective values.

Create table #test1 (Id int,Name varchar(10),Country varchar(10))

insert into #test1 values ( 1,'JOHN','USA'),

select * from #test1

I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:

ID values

Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic

select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1

View 2 Replies View Related

SQL Server 2008 :: Float Value Converting Before Concatenate Get Rounded?

Mar 30, 2015

In My table formula(varchar) and value(Float) column.


formula --> (A+B)/C

I am trying to create same expression with value But my below example showing value gets rounded if it is big.

I am trying to do,

Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456

any way to keep same value as It is?

View 5 Replies View Related

SQL Server 2008 :: Write Query For Date Logic?

May 25, 2015

I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?

I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?

INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'

View 1 Replies View Related

SQL Server 2008 :: Logic To Split Monthly Numbers

Sep 22, 2015

I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.

create table #Invoice
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 Float,


View 3 Replies View Related

Concatenate Column Value In 2008 R2

Oct 7, 2014

Table Item.ItemCategory:

CategoryID Category BelongsTo

35 Electronics 0

39 Digital Camera 35

40 DSLR Lens 39

41 Canon 40

Expected Output:

CategoryID Category BelongsTo

41 canon->Dslr Lens->Digital camera->Electronics 40


As i need to concatenate column value with dependent belongings just like if i pass canon categoryid in where conidition,so desired result should be canon->Dslr Lens->Digital camera->Electronics because As 'canon' belongs to categoryid- 40(DSLR Lens) then 40 belongs to categoryid-39(Digital camera) then 39 belongs to categoryid-35(Electronics) in single select statement...

View 1 Replies View Related

Concatenate Rows

Sep 20, 2007


I have a table similar to the following:

Date ID Name Job Number JobType
12/12/2007 123456 Fred Smith 111111 Full Day
12/12/2007 654321 Bob Blue 222222 Half Day AM
12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:
Date ID Name Job Number JobType
12/12/2007 123456 Fred Smith 111111 Full Day
12/12/2007 654321 Bob Blue 222222 Half Day AM
12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?


View 9 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

Concatenate Multiple Rows In One Row

Mar 11, 2008

i have a table

View 1 Replies View Related

Concatenate Multiple Rows Into One Column?

May 7, 2008

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like the query result to look like this:1....First Assignment....05/01/2008....553422....Second Assignment...05/03/2008....33456,523433....Third Assignment....05/07/2008....35225,45121,11553Any suggestions would be appreciated!

View 5 Replies View Related

Concatenate One Field From Multiple Rows?

Oct 18, 2006

I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:

Title - Author - Edition
The Amazing Pixies - A. N. Author - Hdbk
The Amazing Pixies - A. N. Author - Pbk

Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.

View 2 Replies View Related

SQL 2012 :: Concatenate A Filed If Rows Have Same ID?

Jul 23, 2015

I want to CONCATENATE a field if the rows have the same Id. For example the data looks like

ID Name Identifier
1 ken English
1 Jack French
2 Jamie Spanish
3 Shirley German

The data should look like this if the IDs are same. I am concatenating the Identifier column with the name if the ids are same

ID Name Identifier
1 Ken English English
1 Jack French French
2 Jamie Spanish
3 Shirley German

View 8 Replies View Related

Concatenate Multiple Rows Into One Column?

May 7, 2008

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:


There could be multiple employees for each assignment. Sample data:

1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008


I would like the query result to look like this:

1....First Assignment....05/01/2008....55342
2....Second Assignment...05/03/2008....33456,52343
3....Third Assignment....05/07/2008....35225,45121,11553

Any suggestions would be appreciated!

View 12 Replies View Related

How To Concatenate Multiple Rows Into One Field?

Jul 20, 2005

Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri

View 4 Replies View Related

Transact SQL :: Concatenate Description In 2 Rows

Oct 7, 2015

 I have a requirement .Where i might have multiple rows one of the row will have description and second row might be related description but wiht routning number null .I want to concatenate these rows



View 8 Replies View Related

SQL Server 2008 :: Concatenating Many Rows Into One

Jul 14, 2015

I have a table called Signatures

It contains the fields

Parentobject and [View As]

there can be more than one Parentobject so I want to concatenate them so I have them on one line

for example

901 Joe Dow
901 Jane Dow

I want one line - | 901 | Joe Dow, Jane Dow

I found something similar as below but I'm getting dups like

901 |Joe Dow , Joe Dow
901 | Jane Dow, Jane Dow

DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values
(SELECT @Delimiter + s1.[View As]
FROM Signatures s2

[Code] .....

View 3 Replies View Related

SQL Server 2008 :: INSERT INTO Not Inserting Enough Rows

May 22, 2015

I've got a piece of code that returns 53 records when using just the SELECT section.When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).

The code for creating the table is here:-
USE [CDSExtractInpatients6.2]
/****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/


I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.Don't know if it makes any difference, but the server is running SQL2008.

View 9 Replies View Related

SQL Server 2008 :: In Query - All Rows With Same Status

Oct 28, 2015

I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?

View 6 Replies View Related

SQL Server 2008 :: Transpose Columns To Rows?

Oct 29, 2015

I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.

I have data in the form of:

Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,

The output should look like:

Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black

View 2 Replies View Related

SQL Server 2008 :: Select Alternate Number Of Rows?

Jan 28, 2015

A simple Query to select alternate rows from a table ?

View 9 Replies View Related

SQL Server 2008 :: Concatenating Multiple Rows (Certain Columns)

Jan 29, 2015

Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.

IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015

Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015

View 4 Replies View Related

SQL Server 2008 :: Putting Rows Together To Get Total Times

Jan 30, 2015

I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:

Function | Employee | DateTime
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM

Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2

Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.

View 8 Replies View Related

SQL Server 2008 :: Merge Multiple Rows Of Same ID Into Single Row

Feb 19, 2015

I need the requirements of merging multiple rows of same ID as single row.

My Table Data:


Required Output Should be,


How to achieve this output. Tried grouping but its not working also producing the same result.

View 1 Replies View Related

SQL Server 2008 :: Compare Dates In Rows Of A Table?

Apr 8, 2015

I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.

Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000

View 2 Replies View Related

SQL Server 2008 :: Combine Multiple Rows To Single Row?

May 24, 2015

How to combine multiple rows to single rows for the below sql query.

SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')

The reult for the above query is:

1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1

I need the result like the below

1 10763 01/04/2015 08:00 19:46

View 2 Replies View Related

SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies View Related

SQL Server 2008 :: Delete Duplicate Rows From ANY Table

Jul 31, 2015

Is there is 'fairly' simple query to delete duplicate rows from ANY table ?

A script which removes duplicates from a table, when a table_name is supplied.

View 9 Replies View Related

SQL Server 2008 :: Update The Document Number Row For 3k Rows?

Aug 4, 2015

I have a table where I would like to update the document number row for 3k rows. The problem I have is that the documents come in sets of two (version 1 and 2) but both have different numbers. Picture it like this below:

DOCNUM: 4445787 Version 1
DOCNUM: 4445790 Version 2

It should be the same docnum (ie 4445787 Version 1, 4445787 Version 2).

The challenge is how can we assign the new docnum for version 1 to be also for version 2 as well. Basically in SQL we need a way to

1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.

2. Update them so that the docnums match.

View 7 Replies View Related

SQL Server 2008 :: BULK INSERT Inserting No Rows

Aug 7, 2015

I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.

Here is the code for the operation (only the parameters for the insert itself):

set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''
, codepage = ''1252''
, keepnulls);'

The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.

The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.

View 4 Replies View Related

SQL Server 2008 :: Best Way To Optimize View That Contains Millions Of Rows?

Aug 26, 2015

I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.

CREATE VIEW [dbo].[V_Reqs]
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,


View 3 Replies View Related

SQL Server 2008 :: Check For Missing Rows In A Table

Oct 20, 2015

I have a table of languages, identified by a lang_id column as follows:


I also have a RESOURCES table of phrases (for translation purposes) similar to this:

res_id res_lang res_phrase
AccessDenied en Access Denied

For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.

For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.

View 6 Replies View Related

SQL Server 2008 :: Creating Rows Between Dates In Single Statement

Apr 21, 2015

I am trying to find an easy way to create multiple of just two date in a single sql statement.


A statement using the parameters

@StartDate = '2015-01-01'
@EndDate = '2015-01-05'

Ends up with rows:


What would be the best way to do this ?

View 3 Replies View Related

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