June 7, 2011 at 11:02 am
BEGIN
create table #temp
(
col1 char(2),
col2 char(2),
col3 char(2)
)
insert #temp
values('a','','')
insert #temp
values('','b','')
insert #temp
values('','','c')
END
how to get the out put as "abc" from the above code
June 7, 2011 at 11:21 am
Looks like homework...
Lookup the NULLIF and COALESCE functions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 11:24 am
I'm not sure why you would ever have to do something like this unless you had a bad data model to start with but also followed some specific rules. Honestly, this looks like a homework question versus something that would happen "real world".
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
June 7, 2011 at 12:17 pm
aNULLNULL
NULLbc
NULLNULLd
i need out put as "abcd" from above data.please help how to get this done
June 7, 2011 at 12:21 pm
Here is the answer:
SELECT 'abcd'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 12:29 pm
hi opc . i hope u r funny but the above answer will not suits i think
June 7, 2011 at 12:34 pm
It was a joke, but so are your posts, I thought we were being funny 😛
Why don't you address ChazMan's thoughts about this not being a real-world scenario?
If it's homework, that's fine, but show us what you've tried so far. This site is good for learning, but it's not intended for people to get their homework done for them so all they learn is how to use an online forum...what will happen on test day, or on the job!?!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 12:52 pm
hi opc .i Got the Answer with below logic but is there any another best way to achieve this
BEGIN TRAN
create table #temp
(
col1 varchar(2),
col2 varchar(2),
col3 varchar(2)
)
insert #temp
values('a','d','')
insert #temp
values('','b','')
insert #temp
values('','','c')
declare @listValues varchar(580)
create table #tempfinal
(
result varchar(10)
)
insert into #tempfinal
select col1 from #temp where isnull(col1,'')<>''
union
select col2 from #temp where isnull(col2,'')<>''
union
select col3 from #temp where isnull(col3,'')<>''
DECLARE @STR VARCHAR(2000)
select @STR = COALESCE(@str , '') + result
from #tempfinal
SELECT @STR
drop table #temp
drop table #tempfinal
ROLLBACK
June 7, 2011 at 1:26 pm
That is one way to do it and it is very nice that you decided against using a loop. I suspect there are hundreds of ways to get the task done, with and without using a loop, but "best" is too subjective for me to comment. Suffice it to say that you got the right answer in a set-based manner so you would get an A+ from me 😉
Are there any other requirements to your assignment?
We can do this with one query as follows:
WITH cte(col, row_num)
AS (
SELECT col1,
ROW_NUMBER() OVER (ORDER BY col1)
FROM (
SELECT col1
FROM #temp
WHERE NULLIF(col1, '') IS NOT NULL
UNION
SELECT col2
FROM #temp
WHERE NULLIF(col2, '') IS NOT NULL
UNION
SELECT col3
FROM #temp
WHERE NULLIF(col3, '') IS NOT NULL
) tbl
)
SELECT [1] + [2] + [3] + [4]
FROM cte PIVOT ( MAX(col) FOR row_num IN ([1], [2], [3], [4]) ) AS P_ ;
PS if you stay with your solution you can remove unused variable @listValues.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply