Dynamically Concatenating Multiple Columns In Sequence?
Oct 16, 2014
I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths.
eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
---------
Table BSadIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
---------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN--> 10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif
then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
View 2 Replies
ADVERTISEMENT
Oct 16, 2014
I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR--> 1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
View 2 Replies
View Related
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.
current:
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
Dec 13, 2007
Hi how would i concatenate columns from my database into one column, these are the columns im trying to concatenate;
SELECT Companies.companyName, Companies.companyAddressLine1 , Companies.companyAddressLine2 as Company, Companies.companyAddressLine3, Companies.companyTown, Companies.companyPostcode, AS COMPANY
Thank you
View 7 Replies
View Related
Mar 3, 2004
I have two columns of type integer that I want to concatenate. I also want to put a "/ " in between the columns.
I have tried doing it in the same way as I would for varchar columns but that doesn't seem to work for integers.
Thanks
Rupal
View 2 Replies
View Related
Aug 4, 2006
We have a table in DB2, which has a column1 (Data type: - decimal (10, 0)) and column2 (Data type: - varchar (70)), I need to concatenate these two columns into one while displaying output
Procedure I followed:--
Tried to convert column1 to varchar using cast function so that I could concatenate both columns. However I ended up in getting the following error message
DB2 SQL error: SQLCODE: -461, SQLSTATE: 42846, SQLERRMC: SYSIBM.DECIMAL; SYSIBM.VARCHAR
Message: A value with data type "SYSIBM.DECIMAL" cannot be CAST to type "SYSIBM.VARCHAR".
Please do let me know where I went wrong
Thanks
Pratibha Surya
View 2 Replies
View Related
Oct 19, 1999
I have a table on two different servers, the only difference that I can see is that on server A columns first (varchar 32) and last (varchar 32) have ANSI_PADDING set ON and on server B those columns are OFF. No idea why this is true: I didn't specify that the table be set up this way and they both followed similar creation/upgrade paths.
I execute "select last+first from <table>" on server A and the result looks like:
<last1> <first1>
<last2> <first2>
...
On server B I get
<last1><first1>
<last2><first2>
Now the docs say ANSI_PADDING has nothing to do with this behavior; in fact if I copy the data on server B to 2 new columns with ANSI_PADDING ON I get the same results. But that's the *only* thing that was different in syscolumns. What is causing the different output behaviors on these two servers? Thanks.
View 1 Replies
View Related
Nov 18, 2004
I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?.
Example:
A query may currently return:
Title1 - Subtitle1 - Author 1a - etc
Title1 - Subtitle1 - Author 1b - etc
Title1 - Subtitle1 - Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc
When I would like
Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc
My actual SQL code, if you are interested, is:
SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number,
dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb,
dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors
FROM dbo.edition INNER JOIN
dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN
dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN
dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN
dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN
dbo.party ON dbo.role.party_id = dbo.party.party_id
View 1 Replies
View Related
May 30, 2008
Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example:
ID.....Code
5......33
5......23
ID.....Code...Result
5......33.....33 23
5......23.....33 23
I need the code to get the Result field. I know the code if you were to find the sum, min, max, etc...
(SELECT SUM(CODE)
FROM Table
WHERE ID = Table.ID) AS Result
But I don't know how to write it so it will combine strings.
View 5 Replies
View Related
Jul 13, 2007
I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS.
In other words I have a table as such:
1 John Smith
2 Jane Doe
3 Matthew Jones
And I'd like to create one textbox that contains the following:
"John Smith, Jane Doe, Matthew Jones"
I've been drawing a blank. Anyone have any ideas?
Levi
View 4 Replies
View Related
Jan 17, 2008
Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
View 2 Replies
View Related
Oct 5, 2015
I have a requirement where I have the following separate tables:
Table A:
FldA FldB
34
35
43
53
54
55
64
74
75
Table B:
FldC FldD
1Break Begin
2Break End
3Out
4In
5Dept
Desired Result:
FldA FldD
3 In;Dept
4 Out
5 Out;In;Dept
6 In
7 In;Dept
I have played around with the newly discovered 'for xml path' but I can't quite seem to get the sql syntax right.
View 2 Replies
View Related
Oct 7, 2015
SQL code for the following? (SQL Server 2008 R2 - SQL Server 2012).
I have Table1 Containing two fields with the below entries
VehicleType Name
Two Wheels Bicycle
Two Wheels Scooter
Two Wheels Motorcycle
Four Wheels Sedan
Four Wheels SUV
Four Wheels Pickup
Four Wheels Minivan
The result I'm looking for would be
Table2
Vehicle Type
Two Wheels Bicycle, Scooter, Motorcycle
Four Wheels Sedan, SUV, Pickup, Minivan
View 1 Replies
View Related
Oct 17, 2005
I am currently undertaking a review of the primary keys in a SQL Server2000 database with a view to improving performance of queries.I have heard that, in the case of compound primary keys, it isimportant to select the correct order for the columns within the key.For instance, imagine a table called OrderLine which has primary keycolumns as follows-LedgerOrderNumberOrderLineNumberThe theory I have heard is that columns with the most distinct valuesshould come first. In this case, Ledger is likely to have a maximum of6 distinct values, OrderNumber a maximum of 10 million and OrderLine upto 99. Based on this supposition I believe the best order would be-OrderNumberOrderLineNumberLedgerI have performed a few rudimentary tests that appear to bear this out.I was wondering if anyone else has tried something similar and if sowhat was the result?Thanks,Ross
View 14 Replies
View Related
Jul 6, 2006
Hello,I am learning SQL Server and I would like to ask you a question. Couldyou tell me please if sequence of columns in a table has influence onperformance?Thank you very much/RAM/
View 3 Replies
View Related
Jun 29, 2015
I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
View 4 Replies
View Related
Jun 29, 2015
I have a question  in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
View 3 Replies
View Related
Feb 18, 2004
I've got the stored procedure which first creates and then starts a job.
This stored procedure can be invoked from a number of triggers (after insert, after update)
The problem is:
when a sequence of statements is being executed one by one (like an insert immediately followed by several updates) and the stored procedure is invoked from the propper insert or update trigger once for each statement, the jobs are created in the right order (first for insert statement, then for updates in the order of the initial statements), but are executed in the wrong order.
Is there any explanation to this? And any solution?
The order of such jobs execution is vital for my application.
Thanx a lot in advance.
Please see the enclosed screenshot for an example list of jobs. Name of the job contains time of it's creation and another column shows the time the job was executed.
View 7 Replies
View Related
Sep 3, 2006
Dear All,
I have a requirement, in which i have to add columns dynamically depending the records of a master table in sql server 2000.
Pl, do help me
View 2 Replies
View Related
Sep 21, 2015
I have a sequence container in my Package and this sequence has more than one control flow tasks.
Can I create the checkpoints such that only the failed component inside the sequence container runs again and not the other successful components/tasks in the sequence container?
View 3 Replies
View Related
Dec 10, 2005
I've seen several posts that begin to address this problem, but havenot found a simple, elegant solution that will accomplish this goal.The important part of this solution is that it must be completelydynamic - I have over 40 different categories of devices, each withdifferent fields, and each search will return only one category. Ihave no knowledge of the number or datatype of these field namesbeforehand and must use the sp to dynamically create the table and thentranspose the data.Here is an exampleI have normalized data in this format (this is a simplification)deviceId fieldName fieldValue1 color red1 shape square1 weight(kg) 2.02 shape round2 weight(kg) 1.53 color blue3 shape oval3 weight(kg) 1.0I would like to convert this to the format: (note that it must handlenulls - deviceId 2)deviceId color shape weight(kg)1 red square 2.02 round 1.53 blue oval 1.0Anyone with any thoughts on how best to accomplish this?thanks,Matt
View 19 Replies
View Related
Apr 29, 2008
Hi,
how to dynamically create columns for a table
View 2 Replies
View Related
May 8, 2007
Hi everyone
Is it possible to search dynamically in table columns without using dynamic SQL (i.e. EXEC or sp_executesql)?
I wanna exec FREETEXT(<column_name>, @searchPhrase) by giving columns' names as paramater to stored proc. Is it possible to avoid using dynamic SQL?
Thanks in advance
View 8 Replies
View Related
Mar 10, 2008
I have a report that displays data based on the last 12 months. Is there a way I can order the columns (header and data) based on the month it was run. eg. If I were to run the report in March, I want the columns to be ordered like this:
MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB
If run the report in April, I want the columns to be ordered like this:
APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR
So, the columns in the report are always ordered (12 months backward) based on the month it was run.
View 4 Replies
View Related
Sep 9, 2014
I have multiple sequence containers in my package. Â I only want to have one sendmail task for the failure/completion of the package. Â If I put the sendmail task in the last sequence container and the first seqence fails, the sendmail task will not be reached and therefore, no email will be sent out.Is there a way to have one sendmail task for all the sequence containers and allow it to send mail regardless of what sequence fails/completes?
View 4 Replies
View Related
Aug 3, 2004
Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
Thanks in advance!
View 5 Replies
View Related
Mar 5, 2008
Hi,
I am wondering if someone has some experience with hiding columns in a Matrix report. I have got two details columns: "Yr to Yr Credits Growth€? and "Yr to Yr Credits Growth %" and those two columns return only one value "N/A" for the earliest year since there is nothing to compare to. Thus, I don't really need them for the earliest year. On top of detail columns, I have got three matrix groups: matrix1-Year, matrix1-Quarter and matrix-Date. Once those two detail columns are hidden, I would like obviously resize (shrink) those three matrix groups columns to reflect the fact that the detail columns were hidden.
Thanks!
View 3 Replies
View Related
Nov 19, 2007
Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
any help is appreciated..
Regards
Karen
View 9 Replies
View Related
Apr 29, 2008
Hello:
I have an OLEDB source that uses a stored procedure which pivots records and returns me data with columns which are dynamic (Changing every time). How can I export this data with dynamic number of columns to excel destination?
Thanks
Jatin
View 3 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
May 16, 2015
I have SSRS report that has around 80+ columns. I have requirement where in dynamically hideshow columns in report based on user selection. I could able to do it by setting expression for "Visiblity" property and having report parameter thro' which columns to display can be choosen.
My problem is 2 points
1. fox example if columns 2 and 4 to be hidden, then there is an empty column between 1 and 3 and 5 columns. How to avoid this
2. When i export to PDF / Excel these spaces prevail.
View 3 Replies
View Related