Hi everyone I'm trying to get 4 columns from my table to be on column, not sure how to go about it.
The column names are
X_POLICY_NO,
X_POLICY_EFCTV_DT,
PRODUCT_ABBR,
X_ASCO_CD
I would like to take those 4 columns in the select statement and make them on, I did in access but I tried
Select
(X_POLICY_NO, X_POLICY_EFCTV_DT, PRODUCT_ABBR, X_ASCO_CD) as CONCAT but i get an error that says :
Incorrect syntax, thanks for the help its greatly appreciated
Hi,
i want to select 2 integer and a varchar fields as one. How can i do it?
Any ideas?
thanks
Hi peso, hi KH, Hi everyone....
Declare @day int
set @day = 3
select right('0' + @day, 2)
My expected result is : 03
If single integer - will append in the right a '0'.
If 2 digit integer - will return the actual value.
Thanks.
-Ron-
"If you can only access one site on the Internet, make it SQLTeam!"
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...
thx in adv
Hi,
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.
Thanks for any help.
Mongo
Hello,
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
-- Start Sybase
BEGIN
CALL DBA.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' + CHAR(13) + CHAR(10) +
'2' + CHAR(13) + CHAR(10) +
'3' );
END;
-- End Sybase
-- Start Oracle
BEGIN
ish.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' || CHR(13) || CHR(10) ||
'2' || CHR(13) || CHR(10) ||
'3' );
END;
/
-- End Oracle
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?
thanks beforehand,
Coen Dunnink
The Netherlands
how can i CONCAT 2 columns & () ?
SELECT CONCAT(A,B) AS C From myTAble
but I want to get A (B)
dog (red)
thank you
I'm trying to get month/year from column X_POLICY_EFCTV_DT, i tried this
Concat(month(rr.X_POLICY_EFCTV_DT),
year(rr.X_POLICY_EFCTV_DT))
But i'm getting an error concat is not a recognized function name
is there a better way to get month/year from that column?
i have dataset like following:
ID name
============================
1 John
2 Dave
what i wan to do is show "John,Dave" in a single textbox, what should i do ? do i need a matrix table?
thx,
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:
Specification
------------------
Model 1, Model 2, Model 4
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
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?
Eg: PERCENTAGE
30.00%
50.00%
will this work to create a whole date if YEAR_OPENED is a solid year. Like 1957 for example?
org_date_founded = YEAR_OPENED+'01-01'
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@
View 1 Replies View RelatedHello,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
View 11 Replies View RelatedHi,
I try to create a string that represant a insert statement but I've some problems to concat :
Set @Insert = 'INSERT INTO DEMANDES VALUES (' + @AvionID + ',' + @DemandeID + ',' + @Consommation + ',' + @Vitesse + ',' + @TailleReservoire + ',''' + @PlageHoraireDebut +''','''+ @PLageHoraireFin+''','''+ @VilleDepart+''',''' +@VilleDestination+''');';
AvionID is a int
DemandeID too
Consommation too
Vitesse too,
TailleReservoire too,
PlageHoraire varchar and ville too.
Can you explain me how to concat these strings?
Thanks for your help!
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
The tables look like this:
Table1
PHP Code:
create table user.dbo.table1
(
pk1 int primary key,
fk1 int,
field1 varchar(32),
FOREIGN KEY fk1 REFERENCES table2 (pk2)
)
Table2
PHP Code:
create table user.dbo.table2
(
pk2 int primary key,
field2 varchar(32)
)
table1
PHP Code:
pk1 fk1 field1
1 1 'test1'
2 2 'test2'
3 1 'test3'
table2
PHP Code:
pk2 field2
1 NULL
2 'tab2 test'
I expect to see:
PHP Code:
ViewField
test1-
test2-tab2 test
test3-
I cant find a clear answer to the syntax of concat in for SQL 2005. Can anyone help.
View 3 Replies View Relatedhello,
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...
thanks in advance!
i have 2 table:
t1:
t1_idint
namenvarchar(10)
t2:
t2_idint
t1_idint
Sematnvarchar(50)
then i write following SQL Command
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.
Hi,
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
WeekNbrname testhours
--------------------------------------------------------------------------------
2007/26John"testA, testB" 11
2007/26David"testA" 3
2007/28David"testC" 2
2007/30Victor"testD" 1
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
Anyone could help me please?
Thanks so much in advance
Pierrot
I have a column with consist of customer number.e.g.
c001
c002
c003
How do I add the character "c" to the auto-incremental number everything I add?
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
I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.
Example
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3
The result I want
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3
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
View 11 Replies View RelatedHi,
Can anybody help me to create a single query? I have this problem.
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)
INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)
I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)
(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50
Any help would be greatly appreciated !!
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
View 1 Replies View RelatedHello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
I'm trying to set the concat... option to OFF
all the time and in all my databases
I tried this command
USE master
EXEC sp_dboption 'DatabaseName', 'concat null yields null', 'FALSE'
but it doesn't change anything
Select NULL + 'TOTO'
----> NULL
(it should be 'TOTO')
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"
I think you see what im after, any ideas ?
Regards
Fredrik
I want to concat data from few column to 2 column.
My database as below:
id name email
1 name1 name1@email.com
1 name2 name2@email.com
2 name21 name21@email.com
2 name22 name22@email.com
Output:
id name email
1 name1,name2 name1@email.com,name2@email.com
2 name21,name22 name21@email.com,name22@email.com