June 19, 2012 at 5:21 am
Hi,
I have a requirement to concatenate the values in the columns - BOOKING_EQUIP and GEN_COMMOD_CD grouped by BOOKING_ID.
BOOKING_ID BOOKING_NUM TRADE_CD BOOKING_EQUIP GEN_COMMOD_CD
11515225 LEH100511 EURUSEC 1 20D86, LOUDSPEAKERS,
11515225 LEH100511 EURUSEC 1 40D86, MICROPHONES,
The solution for the above should be like this:
BOOKING_ID BOOKING_NUM TRADE_CD BOOKING_EQUIP GEN_COMMOD_CD
11515225 LEH100511 EURUSEC 1 20D86, 1 40D86, LOUDSPEAKERS, MICROPHONES,
Does anyone know how to achieve this ?
Thanks,
Paul
June 19, 2012 at 6:03 am
Inner join your table on its self on booking_ID next select the columns you want from table a and then concatenate a+b like so
select A.BOOKING_ID , A.BOOKING_NUM , A.TRADE_CD , a.BOOKING_EQUIP+', '+b.BOOKING_EQUIP , a.GEN_COMMOD_CD+,', '+b.GEN_COMMOD_CD
from TABLE_NAME A (nolock)
inner join TABLE_NAME B (nolock) on a.BOOKING_ID =b.BOOKING_ID
***The first step is always the hardest *******
June 19, 2012 at 8:35 am
SGT_squeequal (6/19/2012)
Inner join your table on its self on booking_ID next select the columns you want from table a and then concatenate a+b like so
select A.BOOKING_ID , A.BOOKING_NUM , A.TRADE_CD , a.BOOKING_EQUIP+', '+b.BOOKING_EQUIP , a.GEN_COMMOD_CD+,', '+b.GEN_COMMOD_CD
from TABLE_NAME A (nolock)
inner join TABLE_NAME B (nolock) on a.BOOKING_ID =b.BOOKING_ID
Why the sudden introduction of NOLOCK hints?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:38 am
i always use nolock 🙂
***The first step is always the hardest *******
June 19, 2012 at 8:47 am
SGT_squeequal (6/19/2012)
i always use nolock 🙂
You basically just stated that you always prefer to get dirty reads at the cost of data integrity. I would HIGHLY recommend that you not do that. It is by no means a magic "go fast" pill. It can introduce bugs that are nearly impossible to track down because they are nearly impossible to reproduce and in certain situation you can corrupt your entire database. There are times that dirty reads are acceptable but you should consider using isolation levels instead. As a blanket statement to always use NOLOCK is just plain dangerous.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 9:06 am
Paul, how many rows max will you have to roll up like this? Your example shows two rows - can it be 3, or 4, or n?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2012 at 9:16 am
@sean Lange
It all depends on the task in hand, so in answer to this thread i gave some example SQL to help with his question, up to the user how they use it.
with or without nolock well there are advantages and disadvantages for both
***The first step is always the hardest *******
June 19, 2012 at 9:25 am
SGT_squeequal (6/19/2012)
@Sean LangeIt all depends on the task in hand, so in answer to this thread i gave some example SQL to help with his question, up to the user how they use it.
with or without nolock well there are advantages and disadvantages for both
True. My concern is offering this in a solution to someone and not explaining what it is. They may take this and use it in this query and then continue using it in other queries because they got it from this really smart guy that helped me on SSC. If you understand NOLOCK and the implications that is fine but for others it is just a horrible and incredibly dangerous thing. I also cringe when you state that you always use it and had no explanation of why.
I once worked as a consultant on a financial system and one of their DBAs mandated this hint be on every query. The only explanation given was because it prevented locks and made things faster. They actually had a project to add that hint to every single query in the entire system. It was removed several months later when they corrupted tons of data as result and drove thousands of accounts negative. This cost the company countless dollars and hours to unravel, all because somebody had the notion that NOLOCK makes things faster. I guess I have a particular sour spot for that hint because I have seen first hand how bad it can be when left unchecked.
On the other hand if the usage can be explained for a given query then by all means go for it.
/soapbox off
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 9:27 am
Aye agreed
***The first step is always the hardest *******
June 19, 2012 at 9:37 am
SGT_squeequal (6/19/2012)
Inner join your table on its self on booking_ID next select the columns you want from table a and then concatenate a+b like so
select A.BOOKING_ID , A.BOOKING_NUM , A.TRADE_CD , a.BOOKING_EQUIP+', '+b.BOOKING_EQUIP , a.GEN_COMMOD_CD+,', '+b.GEN_COMMOD_CD
from TABLE_NAME A (nolock)
inner join TABLE_NAME B (nolock) on a.BOOKING_ID =b.BOOKING_ID
Thanks for your reply. However, my query uses multiple tables and when I try to use your solution I get multiple rows of data instead of a single row. One other thing is BOOKING_EQUIP is already a concatenated column.
Thanks,
Paul
June 19, 2012 at 9:43 am
arrr ok well i could only answer in what you put perhaps you can add some DDL to this thread representative data and what you expect the output to be, based of your fisrt set my query would be ok.
***The first step is always the hardest *******
June 19, 2012 at 9:45 am
pwalter83 (6/19/2012)
SGT_squeequal (6/19/2012)
Inner join your table on its self on booking_ID next select the columns you want from table a and then concatenate a+b like so
select A.BOOKING_ID , A.BOOKING_NUM , A.TRADE_CD , a.BOOKING_EQUIP+', '+b.BOOKING_EQUIP , a.GEN_COMMOD_CD+,', '+b.GEN_COMMOD_CD
from TABLE_NAME A (nolock)
inner join TABLE_NAME B (nolock) on a.BOOKING_ID =b.BOOKING_ID
Thanks for your reply. However, my query uses multiple tables and when I try to use your solution I get multiple rows of data instead of a single row. One other thing is BOOKING_EQUIP is already a concatenated column.
Thanks,
Paul
Well certainly with as much time as you have spent on this site you know that we need to see ddl and sample data. Post that along with your desired output and we knock this out pretty quick. Otherwise we are just taking a guess at what you might want and what might work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 9:46 am
ChrisM@Work (6/19/2012)
Paul, how many rows max will you have to roll up like this? Your example shows two rows - can it be 3, or 4, or n?
Could be up to 5 rows and may be even more ? but the above solution does't work as I get duplicate rows of data.
June 19, 2012 at 9:53 am
As Sean pointed out, it is difficult to help provide you solution when you haven't provided us with the DDL for the tables, sample data to populate the tables, expected results based on the sample data. It would also help if you showed us what you have done so far in an effort to solve your problem.
June 19, 2012 at 10:04 am
SGT_squeequal (6/19/2012)
arrr ok well i could only answer in what you put perhaps you can add some DDL to this thread representative data and what you expect the output to be, based of your fisrt set my query would be ok.
Thanks,
Please find the DDL for the 2 tables below:
MG_BOOKING_EQUIPMENT_REQ:
-------------------------------------
CREATE TABLE [dbo].[MG_BOOKING_EQUIPMENT_REQ](
[BOOKING_ID] [numeric](10, 0) NULL,
[EQ_CNT] [numeric](3, 0) NULL,
[KL_EQUIPMENT_TYPE_CD] [varchar](5) NULL
) ON [PRIMARY]
-------------------------------------
MG_BOOKING:
-------------------------------------
CREATE TABLE [dbo].[MG_BOOKING](
[BOOKING_ID] [numeric](10, 0) NULL
) ON [PRIMARY]
-------------------------------------
Sample data for MG_BOOKING_EQUIPMENT_REQ:
--------------------------------
INSERT INTO MG_BOOKING_EQUIPMENT_REQ
VALUES ('11515225', '1', '20D86')
INSERT INTO MG_BOOKING_EQUIPMENT_REQ
VALUES ('11515225', '2', '40D86')
INSERT INTO MG_BOOKING_EQUIPMENT_REQ
VALUES ('11515225', '3', '60D86')
INSERT INTO MG_BOOKING_EQUIPMENT_REQ
VALUES ('11515225', '4', '80D86')
--------------------------------
Sample data for MG_BOOKING:
--------------------
INSERT INTO MG_BOOKING
VALUES ('11515225')
-------------------
I am using this query to concatenate the EQ_CNT and KL_EQUIPMENT_TYPE_CD (called BOOKING EQUIPMENT) and then concatenating the values in BOOKING_EQUIPMENT but it does'nt work as I get duplicate values:
-------------------------------------
SELECT DISTINCT MB.BOOKING_ID,
(cast(MBER1.EQ_CNT as varchar) + ' ' + MBER1.KL_EQUIPMENT_TYPE_CD) + ',' + (cast(MBER2.EQ_CNT as varchar) + ' ' +
MBER2.KL_EQUIPMENT_TYPE_CD) + ',' AS [BOOKING EQUIPMENT]
FROM MG_BOOKING MB
INNER JOIN MG_BOOKING_EQUIPMENT_REQ MBER1
ON MB.BOOKING_ID = MBER1.BOOKING_ID
INNER JOIN MG_BOOKING_EQUIPMENT_REQ MBER2
ON MBER1.BOOKING_ID = MBER2.BOOKING_ID
--and MB.BOOKING_ID = '11515225'
ORDER BY MB.BOOKING_ID
-------------------------------------
Thanks,
Paul
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply