SQL Server 2014 :: Dynamically Concatenating Multiple Columns In A 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 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
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 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
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
Jul 30, 2015
WE have a query which pulls revenue by country and client for the last 3 years. Right now we have each year being reported in separate columns but we would like to have the revenues for each year for each client to appear on one row. Below is the current query we have setup.
SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
case when pr.fiscal_year = 2015 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2015 Revenue',
[Code] ....
View 7 Replies
View Related
Jun 23, 2015
I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
View 7 Replies
View Related
Aug 10, 2014
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
[code].....
View 1 Replies
View Related
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Oct 8, 2015
I'm using the SQL indicated below:
SELECT
DISTINCT C.Field1 As 'Group',
A.Field2 As 'Security Object'
E.Field3 As 'User'
FROM TableA AS A
[code]...
However I'm having difficulties concatenating the user field in the horizontal form indicated.
View 3 Replies
View Related
Jul 6, 2015
I have the following table struction, lets call it table A.
bookidstartdate endate
2001 2000-01-01 2000-01-05
3001 2001-01-01 2001-01-02
4001 2002-01-01 2002-01-04
and i want the end result to be look like this in table B.
bookidstartdate endate bookidrowdate bookidlogseqrowsequence
2001 2000-01-012000-01-05 2000_01_01 2001_0 0
2001 2000-01-012000-01-05 2000_02_01 2001_1 1
2001 2000-01-012000-01-05 2000_03_01 2001_2 2
2001 2000-01-012000-01-05 2000_04_01 2001_3 4
3001 2002-02-01 2003-02-02 2000_01_01 3001_0 0
3001 2002-02-01 2003-02-02 2000_02_01 3001_1 1
4001 2002-01-01 2002-01-04 2002-01-01 4001_0 0
4001 2002-01-01 2002-01-04 2002-02-01 4001_1 1
4001 2002-01-01 2002-01-04 2002-02-01 4001_2 2
The script below works but i have a break when datediff (days,startdate, endate) reaches 0. For every bookidm i want to iterate till the datediff is zero then move on to next bookid and do the same thing.
declare @orders table
(
bookid int,
startdate date,
endate date,
rowsequence int
[Code] .....
View 9 Replies
View Related
Jun 21, 2015
So, I have some questions about best practice in SQL Server.
1.) I have PK like this (company TINYINT, store TINYINT, action TINYINT, invoice INT, sn SMALLINT). I know JOINS will work faster with surrogate key but I have only couple of JOINS on that table. I use members of PK in WHERE clause mainly, alone and combined for reporting purpose. Is it always better to have surrogate key because they don't have any meaning and context of data laying in current PK.
2.) In my PK from above I have two candidates for using Sequence object. Invoice start with 1 for every (company,store,action) combination. Sn start with 1 for every (company,store,action,invoice) combination. I would like to know can I implement Sequence object here knowing that Sequence don't support PARTITION BY in OVER clause. From what I red it cannot be done via Sequence but I have to ask.Here is data sample for this PK
company store action invoice sn
----------- ----------- ----------- ----------- -----------
1 1 1 2017 1
1 1 1 2018 1
1 1 1 2019 1
1 1 1 2019 2
1 1 1 2019 3
1 1 2 1 1
1 1 2 2 1
1 1 2 2 2
1 1 2 2 3
1 1 2 3 1
1 1 2 3 2
1 1 2 3 3
1 1 2 3 4
1 1 2 3 5
View 7 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
May 26, 2015
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
View 4 Replies
View Related
Oct 2, 2014
We are setting up a new reporting environment for our client and as part of the deliverable I need to achieve the following two things.
1. Most of our reports are snapshot reports and all the data needed gets populated into transaction DB tables from a batch program. Some of batches run monthly and others on request, we use a scheduling tool to run these batches. For monthly running batches since I know when they run, I have scheduled snapshot report for the respective dates. But the batch that run on request have no fixed schedule so my question : Is there a way I can invoke or kick off a particular SSRS snapshot Report as soon as the batch completes running. For example if a on-request batch program finishes running today at 6PM , I would like to create a snapshot report as soon as the batch finishes running. Is this achievable ?!
2. As I mentioned above most of the reports are snapshot reports and client wants Report history of 10yrs. We have about 170 reports on the whole which run monthly/daily/yearly. Is there a best practice known in maintaining all the reports and where to store them?
We are using MS SQL Server Reporting Services 2014.
View 0 Replies
View Related
Jul 16, 2015
I would like to know how to split the phone number into two columns based on - symbol Dynamically.
for example
Phone Number
123-12323
1234-1222
so output should be
code number
123 12323
1234 1222
View 1 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
Jul 13, 2015
Basically I need to get the SUM of the sum of three columns and all three columns have nulls. To make it more complicated, the result set must return the top 20 in order desc as well.
I keep facing different issues whether I try and use Coalesce, IsNull, Sum, count, anything. My query never returns anything but 0 or NULL regardless of if I am trying to build a CTE or just use a query.
So I'm using Col A to get the TOP 20 in order (which is fine) but also trying to add together the sums of Col A + Col B + Col C for each of the twenty rows...
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
Dec 2, 2013
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
View 1 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
Aug 5, 2014
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
View 1 Replies
View Related
Oct 26, 2015
I've data as below
account period01 period02 period03 period04
1111 null null null null
1112 782 null null null
1113 null null null 345
1114 765 882 67 321
What I want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod, if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. So the output should be like this
account period01 period02 period03 period04 accoutPeriod
1111 null null null null null
1112 782 null null null 01
1113 null null null 345 04
1114 765 882 67 321 04
View 2 Replies
View Related
Mar 2, 2015
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
View 3 Replies
View Related
Jan 20, 2015
How to join 2 heap tables with out any common fields.
for example tbl1 has
col1
1
2
3
4
5
and tbl1 2 has
col2
a
b
c
I want the output like
col1 col2
1 a
2 b
3 c
4
5
is this possible with out using row_number()?
View 9 Replies
View Related
Jan 22, 2015
I have a table containing the following data:
LinkingIDID1 ID2
166202180659253178
166202253178180659
166334180380253179
166334253179180380
166342180380180659
166342180659180380
166582253179258643
166582258643253179
264052258642258643
264052258643258642
264502258643258663
264502258643259562
Within the LinkingID, there are duplicates in ID1 and ID2 but just in opposite columns. I have been trying to figure out a way to remove these set based. It doesn't matter which duplicate is removed. Essentially these are just endpoints and I don't care which side they are on. The solution must recognize the duplicates and not just remove based on every 2nd row.
View 8 Replies
View Related
Mar 18, 2015
I have created row level security on two views and adding these two views to particular role.Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.
Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level) Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?
View 6 Replies
View Related
Apr 8, 2015
i would like to know the best practices to choose the columns which should be used for delta?.If, i consider Customer ID as part of delta.
View 1 Replies
View Related
May 12, 2015
The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.
Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;
Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
adding or moving ')' or '(' are not working.......
select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........
versus
select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value
select * from #temp order by .....
View 3 Replies
View Related