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,",")
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'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')
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
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA
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
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
Explanation:
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...
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
CREATE TABLE #tbl
(id INT IDENTITY(1,1),
item varchar(100))
INSERT #tbl
SELECT 'This'
UNION ALL
[Code] .....
In My table formula(varchar) and value(Float) column.
Example
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?
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View Relatedwhen creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies View RelatedI am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies View RelatedHey all
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Thanks
tibor
hi.Trying to concatenate two columns:select uname+' '+uaddress as NameAdr from tblUserI only get the first field, name!!??The datatype is both nvarchar. 100 and 50 chars long.If I run this query:select uname, uaddress from tblUsertheres no problem.Any suggestions?ThanksSWN
View 2 Replies View RelatedI am trying to concatenate three columns and when one column might be NULL, the result set is NULL. I have set "concat null yields null" to false and the result is the same. Here is my code:
Code:
SELECT lname + ',' + space(1) + fname + SPACE(1) + mname as FullName
TIA
Mike
I want this query to result in a new table that includes all of the information from the existing table, JobFormPressSpecs PLUS an additional Column with concatenated data as called for below. What am I missing?
SELECT (jobformpressspecs.job#) +'-'+
jobformpressspecs.[Form #]
AS Job
FROM jobformpressspecs
Thanks for your help!
David
I just have a question regarding storing values to a column in ms sql 2008.
Why is it that the value I inserted at the column is truncated when selected in a query.
The column for this is created to accept max. values.
-> Message VARCHAR(MAX) NULL
The string which I need to insert is a combination of characters with a length of 14,720.
According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)
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 RelatedI have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.
I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.
I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.
For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.
So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).
I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:
"54","ABC","apple, orange, banana, pear, mango, peach"
Does that make sense?
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:
TableParent
1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008
TableChild
1....55342
2....33456
2....52343
3....35225
3....45121
3....11553
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!
Give this data set;
declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),
[Code] ....
select * from @BO
The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:
declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),
[Code] ....
I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?
View 1 Replies View RelatedHi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:
I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)
There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.
Let me give you an example:
COL1 COL2
RECORD1 1-A HHH
RECORD2 1-A GGG
RECORD3 1-B LLL
RECORD4 1-B MMM
RECORD4 1-B OOO
RECORD5 1-C NNN
Me result should be:
COL1 COL2
RECORD 1-A HHHGGG
RECORD 1-B LLLMMMOOO
RECORD 1-C NNN
It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.
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,",")
HiI want to achieve the following transformation of data using a storedprocedure.Sourcecol1 col2(varchar)-------------------------1 1.11 1.22 2.12 2.22 2.3=================Desired Resultcol1 col2(varchar)--------------------------1 1.1 | 1.22 2.1 | 2.2 | 2.3=====================Thanks in advance. :)- Parth
View 12 Replies View RelatedHow do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....
View 2 Replies View RelatedI try to take the value from row 1 divided by the value from row 2 but it is not working, here is the following:
Here is my table called Trans
ID Period Sales Profit
1 Current 20 5
2 Previous 40 20
I want to take 20 divided by 40(20/40), 5 divided by 20(5/20)
Here is the result I want:
ID Period Sales Sales_Per Profit Profit_Per
1 Current 20 50% 5 25%
2 Previous 40 50% 20 25%
Here is my query:
Select
(t1.Sales/NULLIF(t2.Sales,0) * 100) as Sales_Per
From Trans t1
INNER JOIN Trans t2
on t1.Id = t2.ID - 1
I have table Called as ‘DC_BIL_ActivityLog’ and XML column name is ‘ActivityDescription’ in SQL Server 2012.
The following information stored on that Column. I want to read cancellation date (12/23/2015) using select statement.
<ActivityDescription>
<text value="PCN was initiated for Policy ^1 on 12/07/2015. Cancellation Date is: 12/23/2015. Amount needed to rescind PCN is: $XX.80." />
<link id="1" linkText="GLXXXP2015 12/02/2015 - 12/02/2016" linkType="policy">
<linkId parm="1" value="1140" />
</link>
</ActivityDescription>
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
I'm trying to write a query that will display the formula for a computed column in SQL Server 2008R2.
I have looked here: [URL] ....
and it say (at least I think) that I can look at the Formula property of COLUMNPROPERTY like this:
SELECT COLUMN_NAME ,
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsComputed'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'Formula'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsDeterministic')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Event' AND COLUMN_NAME = 'CurrentAttendance'
I know the column is computed and I can see the formula in SSMS. I wanted to do this in T-SQL. How to get this value?
I have a request to export some table data to excel and the "notes" column (varchar 255) contains multiple lines separated by CR/LF. when I export to excel, the first record with CR/LF messes up the column alignment in excel, throwing off the format from that point on. how can i export to excel so that it preserves these CR/LF. or if not, how can I remove these characters so that excel can handle it?
See attached example
create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')
I want the output as
GTT taken
Check sugar today
I have a table with lots of xml files in one column(more than 1000), like this
1. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
2. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
3. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...each is big
I want to query some values for all to see the duration but now I can only query one of them
declare @bp xml
select @bp=xml
from bloodpressureohneschema
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from(
select
m.c.value('(./bp:time/bp:value)[1]','date') as time,
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as value
from @bp.nodes('/bp:content/bp:data/bp:events') as m(c)
)m
is there somewhere I can make better?
Below is a SQL statement that shows what columns belong to a certain view. My next question is how can I get a third column that shows the column type ?
VARCHAR(100), or UNIQUEIDENTIFIER or INTEGER or TEXT or NVARCHAR(256) or ........????
select top 100 a.name, b.name, from sys.views a inner join sys.columns b
on a.object_id = b.object_id where a.name like '%Case_Times%'
I need to add a calculated column item in the same column. Please see SQL Codes for both existing data and desired outcome.
Product O is added according to:
for 201501 Product O= sum of en_count for product Y,W,N when yrmnth=201501
for 201502 Product O= sum of sum of en_count for product Y,W,N when yrmnth=20150
SQL:
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
[Code] ....
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),
[Code] ....