September 20, 2011 at 2:34 pm
I tried code that someone else posted (but has now deleted). The code above does work. Thank you!!! (I just hadn't read that far when I tried the first code)
Second part - now that I have it working, I want to join two other tables into the mix. Let's call them TABLE2 and TABLE3. The joins will be on ID and Sub ID. Let's just pretend I want random columns (call them FIELD 1, FIELD 2, and FIELD 3 from tables 2 and 3)
Lastly - I want to add another query I've built into this. This query joins ID and subID in a funky way to make a field named 'job-suf' and also concatenates a field called oper_num. (This works by itself - but I'm having a rough day and can't figure out how to add it to the other data.
select ID + '-' + RIGHT('000' + CONVERT(varchar, SUBID), 4) AS 'Job-Suf', STUFF(
(SELECT ', ' + CAST(TABLE1.oper_num AS [varchar])
from dbo.TABLE1
where (ID = TABLE1.ID) AND (SUBID = TABLE1.SUBID) and (TABLE1.complete = 0)
group by oper_num
Order by jobroute.oper_num
for xml PATH('')),1,1,'' ) AS Operations_Concatenated
from dbo.TABLE1 JR
group by ID, SUBID
ORDER BY ID, SUBID
September 20, 2011 at 3:22 pm
Remember my comment about making things easy for people to work on your problems by providing ddl, sample data and desired output. I provided that for you in a previous post. If you want help you are going to have to help us help you. If you aren't sure how to get that info take a look at the link in my signature for best practices on posting questions. I have no idea how the second part relates to the first part. Give me some tables, data and what you want out of it and we can have a go.
_______________________________________________________________
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/
September 20, 2011 at 3:59 pm
Again - it's been a tough day so please go easy on me.
I've revised what I want:
create table #temp
(
ID nvarchar(20),
SubID smallint,
lOC NVARCHAR(3),
Complete tinyint,
Oper_num int
)
insert #temp (ID, SubID, LOC, Complete, Oper_num)
values
('J000000001', 0000, 'W10', 1, 3),
('J000000001', 0000, 'W10', 1, 5),
('J000000001', 0000, 'W10', 1, 16),
('J000000002', 0000, 'W10', 0, 16),
('J000000002', 0000, 'W20', 0, 60),
('J000000002', 0001, 'W10', 0, 40),
('J000000008', 0000, 'W10', 0, 76),
('J000000008', 0000, 'W10', 0, 80),
('J000000008', 0000, 'W10', 0, 93)
('J000000008', 0000, 'W10', 1, 3)
('J000000008', 0001, 'W10', 1, 3)
('J000000008', 0001, 'W10', 1, 10)
('J000000008', 0001, 'W10', 1, 15)
I have a query where I'm stuffing this value in as a concat field and it works well. What I also need is a column next to it that would show the first value of this concat field. (some rows may have null values - so I would want the null in this case)
CONCAT OPER_NUM QUERY THAT WORKS:
(SELECT '..' + CAST(#temp.oper_num AS [varchar])
from dbo.#temp
where (ID = #temp.ID) AND (SUBID = #temp.SUBID) and (#temp.complete = 0)
group by oper_num
Order by #temp.oper_num
for xml PATH('')),1,1,'' ) AS Operations_Concatenated
desired data set: (I want the first oper_num for each combination of ID/subID/Loc where completed=0)
ID, SUBID, LOC, FIRST OPER_NUM, CONCAT OPER_NUM
J000000001, 0000, W10, NULL, NULL (SINCE NONE HAVE A COMPLETE=0)
J000000002, 0000, W10, 16, 16
J000000002, 0000, W20, 60, 60
J000000002, 0001, W10, 40, 40
J000000008, 0000, W10, 76, 76..80..93
J000000008, 0001, W10, 3, 3..10..15
September 21, 2011 at 6:39 am
Just ignore Joe. He hasn't figured out yet that most people post a sample related to their problem instead of posting potentially proprietary code, data that might have legal liabilities, et al. He also has the manners (online at least) of a rabid weasle, despite his tendency to accuse others of violating "netiquette". He's a bit fixated on punchards recently, but he goes back and forth between that and acusing people of trying to treat tables like magnetic tape. It's amusing once you see how shallow it is and how push-button his responses are to just about everything. He occassionally seems to fail the Turing Test even.
With the Col3 definition added, you seem to have what you need. Is that correct? Does it get you what you want from your data, or do you need further help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 21, 2011 at 7:36 am
Thank you GSquared!
I still need help. I was working on finding the first value of a field and then concatenation all values for that field. I am able to get them to work seperately but when I put them together, the first value is coming up with something different. See my post 2 up - is there an easy way to just modify the concat. code to get the first value or do I need to post the full query to see if it's just a grouping type issue?
September 21, 2011 at 9:10 am
Hi,
I combined the 2 codes you supplied and got this
create table #temp
(
ID nvarchar(20),
SubID smallint,
lOC NVARCHAR(3),
Complete tinyint,
Oper_num int
)
insert #temp (ID, SubID, LOC, Complete, Oper_num)
values
('J000000001', 0000, 'W10', 1, 3),
('J000000001', 0000, 'W10', 1, 5),
('J000000001', 0000, 'W10', 1, 16),
('J000000002', 0000, 'W10', 0, 16),
('J000000002', 0000, 'W20', 0, 60),
('J000000002', 0001, 'W10', 0, 40),
('J000000008', 0000, 'W10', 0, 76),
('J000000008', 0000, 'W10', 0, 80),
('J000000008', 0000, 'W10', 0, 93),
('J000000008', 0000, 'W10', 1, 3),
('J000000008', 0001, 'W10', 1, 3),
('J000000008', 0001, 'W10', 1, 10),
('J000000008', 0001, 'W10', 1, 15)
select ID + '-' + RIGHT('000' + CONVERT(varchar, SUBID), 4) AS 'Job-Suf', STUFF(
(SELECT '..' + CAST(#temp.oper_num AS [varchar])
from dbo.#temp
where (jr.ID = #temp.ID) AND (jr.SUBID = #temp.SUBID) and (#temp.complete = 0)
group by ID, SUBID,oper_num
Order by #temp.oper_num
for xml PATH('')),1,1,'' ) AS Operations_Concatenated
from #temp JR
group by ID, SUBID
ORDER BY ID, SUBID
Is this what you are looking?
September 21, 2011 at 9:39 am
If you take that query - what I need is third column that shows the first value that is in the concat field (the one with the "stuff" command).
September 21, 2011 at 10:02 am
iluvmyelement (9/21/2011)
If you take that query - what I need is third column that shows the first value that is in the concat field (the one with the "stuff" command).
What does that mean? Given the above query what is the exact output you want? Can you get what you want with a substring?
Just a total guess but...something like this maybe?
select ID + '-' + RIGHT('000' + CONVERT(varchar, SUBID), 4) AS 'Job-Suf', STUFF(
(
SELECT '..' + CAST(#temp.oper_num AS [varchar])
from dbo.#temp
where (jr.ID = #temp.ID) AND (jr.SUBID = #temp.SUBID) and (#temp.complete = 0)
group by ID, SUBID,oper_num
Order by #temp.oper_num
for xml PATH('')),1,1,''
) AS Operations_Concatenated
, STUFF(
(
SELECT top 1 '..' + CAST(#temp.oper_num AS [varchar])
from dbo.#temp
where (jr.ID = #temp.ID) AND (jr.SUBID = #temp.SUBID) and (#temp.complete = 0)
group by ID, SUBID,oper_num
Order by #temp.oper_num
for xml PATH('')),1,1,''
) AS Operations_Concatenated_FirstValue
from #temp JR
group by ID, SUBID
ORDER BY ID, SUBID
_______________________________________________________________
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/
September 21, 2011 at 11:19 am
Thanks Sean - that's almost there! For some reason it's cutting off the first digit in that new column. (e.g. 16 is showing as 6 for me)
September 21, 2011 at 12:28 pm
iluvmyelement (9/21/2011)
Thanks Sean - that's almost there! For some reason it's cutting off the first digit in that new column. (e.g. 16 is showing as 6 for me)
OK again I will ask you to clarify what you mean. What do you mean that 16 is truncated to 6? There is no "16". There is ".16..60" which becomes ".16". My query was a total shot in the dark because your request was extremely vague.
_______________________________________________________________
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/
September 21, 2011 at 1:42 pm
My mistake - when I was translating everything back from the generic code I had a typo - all is good. Thank you - that was EXACTLY what I wanted!!!
September 21, 2011 at 1:44 pm
WOOHOO!!! Score +1 for bullseye shot in the dark. 😛
_______________________________________________________________
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/
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply