We help IT Professionals succeed at work.

Data Manipulation

the_b1ackfox
the_b1ackfox asked
on
Hello SQL Ninjas,

Is there a way to get data like this:

Bob Barker Blah blah blah blah
Bob Barker Blah blah blah blah
Bob Barker Blah blah blah blah

to look like this?

Bob Barker
       Blah blah blah blah
       Blah blah blah blah
       Blah blah blah blah

Strictly in SQL?
Comment
Watch Question

Lara FEA
CERTIFIED EXPERT

Commented:
yes, you can do this :-)

select 'Bob Barker'
select '       Blah blah blah blah'
select '       Blah blah blah blah'
select '       Blah blah blah blah'
EA
CERTIFIED EXPERT
Commented:
This is may be what you are looking for.
-- populate sample table with data
select
'Bob' c1, 'Barker' c2, ' Blah' c3, ' blah' c4, ' blah' c5, ' blah' c6 into #tmp
insert into #tmp
values ('Bob','Barker','Blah1','blah1','blah1','blah1')
insert into #tmp
values ('Bob','Barker','Blah2','blah2','blah2','blah2')

insert into #tmp
values ('Rob','Barker','Blah1','blah1','blah1','blah1')
insert into #tmp
values ('Rob','Barker','Blah2','blah2','blah2','blah2')


-- get results
-- column rn needed for ordering your results

WITH t_CTE (rn, c1, c2, uname, cnt)
AS
-- Define the CTE query.
(
    SELECT rOW_NUMBER() OVER(ORDER BY c1, c2), c1, c2,c1+' '+ c2 uname , COUNT(*) as cnt
    FROM #tmp
   group by c1, c2,c1+' '+ c2
)


SELECT '             ' name ,c3+','+c4+','+c5 blah
,  (ROW_NUMBER() OVER(ORDER BY t.uname)) + power(100,rn) AS rn
FROM #tmp u join t_CTE t on u.c1=t.c1 and u.c2=t.c2
union all
select  uname,''
,  power(100,rn)  from t_CTE t
order by rn

another approach:


set nocount on

create table #test (s1 varchar(50), s2 varchar(50))

insert into #test values ('Bob Barker','Bla bla bla')
insert into #test values ('Bob Barker','Bla bla bla')
insert into #test values ('Bob Barker','Bla bla bla')
insert into #test values ('Sandy Beach','Bla bla bla')
insert into #test values ('Sandy Beach','Bla bla bla')
insert into #test values ('Sandy Beach','Bla bla bla')

declare @s varchar(max)

set @s = ''

      select @s = @s +
            CASE
                  WHEN row = 1
                  THEN s1 + CHAR(13) + CHAR(10)
                  ELSE ''
            END + REPLICATE(' ', 6)
            + s2 + CHAR(13) + CHAR(10)
      from
            (
            select
                  s1,
                  s2,
                  ROW_NUMBER() OVER(PARTITION BY s1 ORDER BY s2) row
            from #test
            ) a

print @s

drop table #test

the_b1ackfoxTechnophile

Author

Commented:
Both of these approaches work well.  

Trofimoval & jvejskrab:
I will be awarding points as follows.  200 points a piece for the queries, 100 points to Trofimoval for first blood!  I do not fully understand the queries... yet, but I certainly appreciate (and totally love) it when you guys throw something at me that causes me to learn something new.  I am adding this to my knowledge base and will be spending more time with both of these queries this week.  My hat off to both of you!

Fox

Explore More ContentExplore courses, solutions, and other research materials related to this topic.