I am trying to return rows from a query and I want to display the information from 2 columns into one. Here is my query:
SELECT TOP (100) PERCENT dbo.CASES.CaseID, dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber,
dbo.SCANNEDDOCUMENTS.TopicID, dbo.EVENTS.EventTypeID, dbo.EVENTS.ev_StartDate, dbo.SCANNEDDOCUMENTS.Doc
FROM dbo.SCANNEDDOCUMENTS INNER JOIN
dbo.CASES ON dbo.SCANNEDDOCUMENTS.CaseID = dbo.CASES.CaseID INNER JOIN
dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN
dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN
dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID
WHERE (dbo.CASENUMBERTYPES.CaseNumTypeDesc IN ('cc', 'dc')) AND (dbo.EVENTS.EventTypeID IN (7048, 6467)) AND
(dbo.SCANNEDDOCUMENTS.TopicID IN (2, 3, 4)) AND (dbo.EVENTS.ev_StartDate > CONVERT(DATETIME, '2008-01-13 00:00:00', 102)) AND
(dbo.EVENTS.ev_StartDate < CONVERT(DATETIME, '2008-01-19 00:00:00', 102))
ORDER BY dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber
I get this for my return
1526959
CC
228762
2
7048
1/16/2008 12:00:00 AM
<Binary data>
CC is my CASENUMBERTYPES and the next column (228762) is CASENUMBERS
How can I get it to return it to where the CASENUMBERTYPES and CASENUMBERS show together in one column?
Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:SELECT Specification FROM Cars WHERE Model = 112Which would return something like:Specification------------------Model 1Model 2Model 2Model 4 What I'd like is these to be combined into a single row such as:Specification ------------------Model 1, Model 2, Model 4
I want to use a NEWID() to generate order numbers, but i dont want to give customers the long uniqueID. so im wondering if i concatinate it to 8 characters, if that would be safe or not...
I am trying to concat an int with an nvarchar in my select:
select distinct(id + name) from load where name not like '%test%'
but I am getting an error saying: Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.
My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.
I have a string let's say: "123". I want to convert it to: -- Start SqlServer BEGIN {CALL DBA.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' + CHAR(13)+ CHAR(10) + '2' + CHAR(13) + CHAR(10) + 3' )} END; -- End SqlServer
This works well for oracle and sybase, for sqlserver I can't concatenate in a parameter of a stored procedure. I really want to do this. To create a variable is a lot of extra programming. Is there anyone who knows how to cancat these strings within the stored procedure call?
Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:
SELECT Specification FROM Cars WHERE Model = 112
Which would return something like:
Specification ------------------
Model 1 Model 2 Model 2 Model 4
What I'd like is these to be combined into a single row such as:
SELECT ROW_NUMBER() OVER (ORDER BY BM.BILL_NUMBER_V) AS [SL_NO],PP.KID_ID_NO_V AS [KID_NO],(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME], CONVERT(VARCHAR(10),PP.UPDATEDDATE_D,101)AS [VISIT_DATE],BM.BILL_NUMBER_V AS [BILL_NUMBER],CONVERT(VARCHAR(10),BM.BILL_DATE_D,101) AS [BILL_DATE], ROUND(BM.BILL_AMOUNT_M,2) AS [BILL_AMOUNT],ROUND(BM.CONCESSION_AMOUNT_M,2) AS [CONCESSION_AMOUNT],ROUND(BM.TOTAL_AMOUNT_M,2) AS [TOTAL_AMOUNT],
[Code] .....
In the above query i want to concat symbol '%' for output of percentage column. How to do that?
If I have table1 and table2 with table2 having multiple rows tied to asingle row in table 1.What I am trying to do is set up a view that has one row that showsthe followingtable1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,table2.row3.detailI'd like to be able to do a select on the view and only come back withone row per widget. If possible, I'd actually like to be able toconcat all the rows from table 2 into one column if that's possible.table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail- table2.row3.detail), table1.dateCreatedthxM@
Hello,I have a very simple problem which I will illustrate with an example:I have the following records in my table:A 1 CA 2 CA 3 CB 8 KB 9 KI now want to group them and the result has to be:A 1,2,3 CB 8,9 KSo the results in the second row have to be concatenated. I guessthere is no function to do this... What is the simplest solution?Kind regards,Bart Warnez
I want to concat strings such that if one string is null, it is treated as an empty string. For example 'abc' + NULL = 'abc'.
SQL Server has the CONCAT_NULL_YIELDS_NULL defaulted to ON, and it needs to be on in most cases when dealing with indices. (INSERT, DELETE, UPDATE, etc.) However, when I am running a select, and one field has a null value, I still want to see the other fields in the resulting string.
I can do this in a function, but I need a view, and the view doesn't seem to let me save a command with 'SET' in it. (However, it does let me run the command! And it works!) It's just when I try to save it, I get a syntax error, so the view is unavailable to me as I want it.
The command that works, but won't save is:
PHP Code:
SET CONCAT_NULL_YIELDS_NULL OFF SELECT t1.field1+'-'+'t2.field2 AS viewField FROM table1 t1 INNER JOIN table2 t2 ON t1.fk1=t2.pk2
I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column. For example: select letter from alphabet_table a b c d e ... 26 rows returned.
Other than a cursor, how would I write a query to return the following: row1: abcdefghijkl...
SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product FROM dbo.T1 INNER JOIN dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id GROUP BY dbo.T1.t1_id
but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.
I have the following table called "tests" : id WeekNbrnametest hours -------------------------------------------------------------------------- 1 2007/26John "testA"5 2 2007/26John "testB"6 3 2007/26David "testA"3 4 2007/28David "testC"2 5 2007/30Victor "testD"1
I want to write a query so that I have as a result one row per person and per week, as followed
This means that I need to concatenate the values of the test column if in the same week and same person. For now, I have only managed to do the job without the test concatenation the following way:
SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours] FROM tests GROUP BY WeekNbr, name
and I get the following: WeekNbrnamehours ------------------------------------------------------- 2007/26John 11 2007/26David 3 2007/28David 2 2007/30Victor 1
I have a procedure (a) where i call another procedure (b) passing @message as a parameter to procedure B.
Ex: Create procedure a as Begin declare @prevyear exec b @message = 'avvasdva' + cast(@prevyear as varchar) End
When i execute above procedure, i get error at + sign i.e. @message parameter. how can i concatenate string with another parameter when passing parameters
Hi All,Here's a challenge.If there is a 'one word' answer to this, i.e. the name of a built inSQL Server function I can use, that would be great! However...I have a standard 1:m relationship, e.g. tblHeader and tblDetail.I want to create a view of records from 'tblHeader' and within thatview have a column (called e.g. DetailTypes) that provides a singlecomma separated varchar of values from a varchar field (called e.g.DetailType) that appears in related records in the 'tblDetail' table.(hope you understood that :)e.g. the contents of my 'tblDetail' table could be....Detail ID | Header ID | DetailType-----------------------------------1 | 1 | A2 | 1 | A3 | 1 | B4 | 2 | A5 | 2 | C6 | 3 | BTherefore, the view I want to create should return:Header ID | DetailTypes-------------------------------1 | 2A, 1B2 | 1A, 1C3 | 1Bi.e. the first row can be read as "Header 1 has 2 'A' detail recordsand 1 'B' detail record."I have created a view e.g. 'vqryHeaders' which calls a user definedfunction that takes the HeaderID and opens a cursor on another view,e.g. 'vqryDetailGrouped'. The other view groups the records intblDetail so that I can get a count of each DetailType for each HeaderID. The cursor then loops through the returned records concatenatingthe count and detail type into a comma separated string (as shownabove).However, when I run this across 20k records it is soooo sloooooww. Ihave indexes on the relationship fields and I am using realisticallysized varchars - neither made any difference in speed. It isdefinately the function that I wrote that slows it down as the view islightning fast when I remove my function call.I can supply source code if necessary, but I think that this is akind-of generic problem so I don't see the point - yet.I really hope you can help.Regards,Jezz
I'm new to SQL and I've been trying this for a while now.
I have let's say a loop of numbers from one to ten. It appears like this:
1 2 3 4 5
What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.
Hi all. I have been going round and round for the past 2 days on this and would appreciate any help. In a view select statement, I need to concat 2 varchar fields with a text field. If I CONVERT the TEXT field to VARCHAR, only the first 30 characters of the field appear in the result set. If I convert the VARCHAR fields to TEXT, I get an error that I cannot CONCAT TEXT fields. I'm not sure what to do. Can someone please offer some assistance? Thanks in advance! Steve
Hi there i have this: CAST(CAST(FLOOR(CAST(CREATED AS FLOAT))AS DATETIME) AS varchar)+' '+CAST(SUBJECT as varchar)AS DESCRIPTION And it returns data like this "Jun 13 2008 02:00AM My description" I just want to get the "2008-06-13 My description"
I have the CAST(FLOOR(CAST(CREATED AS FLOAT))AS DATETIME) in another sql-question and that works it returns only "2008-06-13"
