Group By Concatenate Without Using XML
Sep 29, 2012
Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?
Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...
For example, if my data was:
1 "A"
1 "B"
1 "C"
2 "D"
2 "E"
I want to group by the first value and concatenate the second value:
1 "A,B,C"
2 "D,E"
Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .
View 2 Replies
ADVERTISEMENT
Aug 4, 2007
I have the following dataset:
State ZIP Homes Schools
WA 98007 2000 4
WA 98052 3000 5
WA 98079 2000 3
Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this.
My report needs to show:
State Homes Schools
[98007, 98052, 98079] 7000 12
Any help will be greatly appreciated
View 6 Replies
View Related
Jul 3, 2015
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 Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Feb 1, 2007
Need help. How to concat into 1 row from the example below:
Table Address
Block Flr Road
2 1 Green
3 - Maine
4 3 Bell
5 2 Prade
Result :
Block 2 Flr 1 Green Road
Block 3 Maine Road
Block 4 Flr 3 Bell Road
Block 5 Flr 2 Prade Road
Cheers!
View 7 Replies
View Related
Aug 31, 2007
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
View 5 Replies
View Related
Oct 1, 2007
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result Thanks
View 2 Replies
View Related
Feb 23, 2001
I'm trying to use a variable (@sqlfields1) for building a select statement.
Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":
If @showo1 = 1
select @sqlfields1 = 'o1,'
If @showo5 = 1
select @sqlfields1 = @sqlfields1 + 'o5'
I want @sqlfields1 to be = 'o1, o5'.
Regards,
Abrazame
View 2 Replies
View Related
Oct 25, 2004
Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'
Is it possible.
Please help :)
View 3 Replies
View Related
Jul 27, 2007
Hey 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
View 14 Replies
View Related
Aug 31, 2007
is it possible to concatenate 2 recordsets ?
i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.
if i use union, all the results will be mixed.
thanks for the help!
View 7 Replies
View Related
Mar 30, 2014
I have a table like so
code remarks
1234 abcd
4567 efgh
I need to concatenate the values in the remarks column into one row like below
abcd, efgh....how is it possible
View 4 Replies
View Related
Jan 25, 2007
I have a table like below.
certid name
1 xxx
1 yyy
1 cvb
now I want to output as xxx,yyy,cvb when i pass input certid=1
I want to get it using a single query statement
View 7 Replies
View Related
Mar 5, 2007
I hope this is the correct place to ask this...
I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:
SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL
But unfortunately I am getting the error:
"Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."
Any idea how to get around this at all just using an SQL query statement?
View 2 Replies
View Related
Mar 27, 2007
-- Prepare sample data
DECLARE@Sample TABLE (ID INT, Code VARCHAR(3))
INSERT@Sample
SELECT290780, 'LT' UNION ALL
SELECT290780, 'AY' UNION ALL
SELECT290781, 'ILS' UNION ALL
SELECT290780, 'AY'
-- Show the expected output
SELECT DISTINCTs1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM@Sample AS s1
ORDER BYs1.ID
SELECT DISTINCTs1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM@Sample AS s1
ORDER BYs1.ID
SELECT DISTINCTs1.ID,
STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM@Sample AS s1
ORDER BYs1.ID
Peter Larsson
Helsingborg, Sweden
View 20 Replies
View Related
Jul 23, 2005
I am beggining to learn stored procedures and I am trying toconcatenate the where clause below but I keep getting an error:declare @sqlwhere varchar(15)set @sqlwhere = 'parentid=2'select * from categories where @sqlwhereThis is the error I am gettingServer: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '@sqlwhere'.What am I doing wrong?ThanksRod
View 6 Replies
View Related
Jul 20, 2005
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 Related
Oct 9, 2007
Hi there,
We are storing dates in a SQL table as dayPublished, monthPublished, yearPublished because sometimes not all the values are available.
I thought something like
select dbo.Resources.monthPublished + '/' + dbo.Resources.dayPublished + '/' + dbo.Resources.yearPublished as fullDate
but it doesn't like my slashes, and if I leave them out it simply adds the three fields together. Boo!
Any ideas?
Thanks,
Megan
View 3 Replies
View Related
May 2, 2008
Hi,
Whats wrong with the following T-SQL?
DECLARE @SQL NCHAR(4000)
SELECT @SQL = 'xxxx'
SELECT @SQL = @SQL + ' yyyy'
PRINT @SQL
I'm only seeing 'xxxx' in the debug window so it appears that its not adding the 2 strings?
Weird?!?
View 8 Replies
View Related
Jan 16, 2008
Hi,
I have a big problem, as I commented in other thread in this forum I Have a DataReader with an MDX query which right now runs out of memory, but I figured that if I made the query by range I could get what I need without running out of memory. However, to achieve that I need to concatenate at the end of a file with a Flat File destination, the result of every query, but I don't know how to do it, It always writes over the data.
View 8 Replies
View Related
Sep 20, 2007
Hi
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?
Cheers
View 9 Replies
View Related
Mar 4, 2008
Hi All,
I have a very simple question which I can't seem to find an easy way of doing in SSIS. (Alternatively I may be having a "Can't see the wood for the trees" moment )
I have a folder where files are deposited on a regular basis . (Every few seconds)
I want to take those files and concatenate them into a single file in another directory ready for processing.
The equivelent command prompt line would be copy c: estfiles*.* c: estfilescatteddata.dat
How would I be best doing this in SSIS? Also I could do with working out the best way to quiesce the folder so I don't get any locked files errors.
Any ideas?
Thanks
Nick
View 7 Replies
View Related
Feb 13, 2008
Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks
View 7 Replies
View Related
Feb 20, 2008
Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.
Code Snippet
UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'
However attempting the same command in T-SQL I get the following error:
Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.
Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.
So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?
Thanks for the help, Onam
View 6 Replies
View Related
May 13, 2015
I have the below problem:
DECLARE @t TABLE
(
ID int,Â
Name nvarchar(255)
)
INSERT INTO @t
SELECT 1, 'Raven'
[Code] ....
Returns the below:
ID Â Â Name
1 Â Â Â Â Raven
4 Â Â Â Â Raven
43 Â Â Â Jack
Any names which are appearing twice I would like to have only one row so concatenate the IDs into one row for each Name, like below:
ID Â Â Â Â Â Name
1, 4 Â Â Â Raven
43 Â Â Â Â Jack
Is this possible?
View 4 Replies
View Related
Nov 27, 2007
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
View 1 Replies
View Related
Nov 6, 2015
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.Â
View 4 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Oct 28, 2007
Hi everyone,
I'm writing a stored procedure where I need to concatenate two XML strings.
I would normally write XMLx = XMLy + XMLz
However because the variables are in XML SQL2005 doesn't like this and if I convert them to strings the XML structure gets lost.
Anyone any ideas ?
Thanks
D.
View 1 Replies
View Related
Mar 11, 2008
i have a table
View 1 Replies
View Related
Mar 19, 2008
I am creating a random-id in this way:
declare
@rand_id intset @rand_id=(SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))
print @rand_id
now I want to add "-07" to the end of the random number generated.
if 12345 is the random number, i want it to be 1245-07.
Any idea how to concatenate the "07" to this random number?
View 4 Replies
View Related
Sep 8, 2000
I would like to concatenate text datatypes. Is this possible??
I'm using field1 + " " + field2
I cannot convert to varchar since the size is larger than 8000.
Thanks, Vic
View 1 Replies
View Related
Aug 28, 2006
Hey Folks,
I have a View that I'm using to power a Crystal Report. I want to include an extra column that will be a string containing all the values from a single column of another table, seperated by comma.
So for example:
Project | Staff
----------------------
TestProject1 | "Jonathan, Greg, Joe"
Is this possible? Where do I do it? From the SQL Server end or in the report?
View 2 Replies
View Related