Displaying Data Accross

[benchpolo]

I need assitance on how to write a TSQL query to display data in one row.

Current Data:
Name,Result
JOHN,90.00
JOHN,95.00
JOHN,98.00

Expected Display
JOHN,90.00,95.0 0,98.00

I just don't know where and how to begin.
Thanks.

[ck9663]

Here, try to start from here...


Good Luck!!!

~~ CK

[Rabbit]

If you don't know how many results there will be or the number may vary from group to grooup, you will probably have to write your own aggregate function or use a cursor in a stored procedure.

[nbiswas]

Hi, I am a bit confuse about the data presented
..whether the input table contains one column (where the values are comma separated) or it has two columns. However, I am presenting the solution for both the cases

Case 1: Assuming the input table has two columns

e.g.

Name Result
John 90.00
John 95.00
John 98.00
Abraham 86.56
Abraham 23.67

Query

Declare @t table(Name Varchar(50), Result decimal (10,2))
Insert into @t 
	Select 'John', 90.00 Union All Select 'John', 95.00 Union All Select 'John',98.00 Union all
	Select 'Abraham',86.56 Union All Select 'Abraham',23.67
Select * from @t

Select Display = Name + ',' + ConcateNamedResult
From

(

Select 
	Name,
	
	ConcateNamedResult = (select ',' + CONVERT(varchar(100), CAST(Result AS decimal(10,2)))  
			from @t t2 where t2.Name = t1.Name
			FOR XML PATH(''))
From @t t1
Group By t1.Name

)X

Case 2: Assuming the input table has one column and the records are already comma separated

e.g.

NameResult
John, 90.00
John, 95.00
John, 98.00
Abraham, 86.56
Abraham, 23.67

Solution

Use a function that will split the strings based on the delimiter[ Comma here(',') ] which will give the output as

Name      Result
John      90.00
John      95.00
John      98.00
Abraham   86.56
Abraham   23.67

and then use the above query
e.g.

DECLARE @t TABLE(NameResult VARCHAR(50))
INSERT INTO @t(NameResult) SELECT 'JOHN,90.00'
INSERT INTO @t(NameResult) SELECT 'JOHN,95.00'
INSERT INTO @t(NameResult) SELECT 'JOHN,98.00'
INSERT INTO @t(NameResult) SELECT 'Abraham,86.56'
INSERT INTO @t(NameResult) SELECT 'Abraham, 23.67'
Select * from @t

;With cte As
(
SELECT substring(NameResult, 1, CHARINDEX(',',NameResult)-1) Name,
substring(NameResult, CHARINDEX(',',NameResult)+1, LEN(NameResult)) Result
FROM @t

)

Select Display = Name + ConcateNamedResult
From

(

Select 
	Name,
	
	ConcateNamedResult = (select ',' + CAST(Result AS varchar(100))  
			from cte c2 where c2.Name = c1.Name
			FOR XML PATH(''))
From cte c1
Group By c1.Name

)X

N.B.~ The solutions will work from Sql Server 2005 onwards.

Hope this helps

[benchpolo]

CODE:

SELECT left(memberkey, 8) as memberkey, submeasure
into #temp1
from cdc
where memberkey = '99999999'

select memberkey, [1] submeasure
from(select * from #temp1
pivot(max(subme asure) for Submeasure in ([1]))p) a

Data: 2 columns -> memberkey, submeasure

memberkey submeasure
99999999 A1c Test
99999999 A1c>9
99999999 A1c<=9
99999999 A1c<8
99999999 Eye Exam
99999999 LDL Test
99999999 LDL<100
99999999 Neph Attn
99999999 BP<140/80
99999999 BP<140/90
99999999 All

I need the memberkey to display only once and the submeasure result to display horizontally. Somehow, the code I wrote is giving me a null value.

[benchpolo]

Ok. So I have another similar complex query that involves member name, mailing address (street, city, state, zip), home address (street, city, state, zip).

I tried the pivot function, but it doesn't work that way i want it too since the mailing and home address are from the same columns. Make sense?

Task
There are two member address Mail and Home address both of these addresses are displayed vertically. Need a code to display the home address side by side with mailing address.

Logic:
1) Mailing address is the default it will always have an address
2) If Home address is NULL use Mailing Address

Original Data

memberid, MLADDRS, MLCITY, MLST, MLZIP
123456790,123 TECHNOLOGY DR, LOS ANGELES, CA, 90001
123456790,222 EAST, LOS ANGELES, CA, 90001

Expected Datw

memberid, MLADDRS, MLCITY, MLST, MLZIP, HMADDRS, HMCITY, HMST, HMZIP
123456790,123 TECHNOLOGY DR, LOS ANGELES, CA, 90001, 222 EAST, LOS ANGELES, CA, 90001

[ck9663]

Is there a code that will tell you which one is mailing and which one is home address? You cannot just say sequential, since depending on index, SQL Server does not really look at records sequentially.


~~ CK

[benchpolo]

Yes. There's a field address_type which holds the value of MAILING and HOME.

[benchpolo]

I was able to write the code, but the dilemma is some records will not have home address some will have mail and home.

select 
a.NAME_ID
,a.MEMBER_ID            
,ltrim(rtrim(a.contact_address1_1)) as contact_address1_1
,ltrim(rtrim(a.contact_address2_1)) as contact_address2_1
,a.contact_city1
,a.contact_state1
,a.contact_zip1 
,ltrim(rtrim(b.contact_address1_1)) as contact_address1_2
,ltrim(rtrim(b.contact_address2_1)) as contact_address2_2
,b.contact_city1 as contact_city2
,b.contact_state1 as contact_state2
,b.contact_zip1 as contact_zip2
,a.contact_salutation1 
,a.member_contact_first_name1 
,a.member_contact_last_name1      
,a.ADDRESS_TYPE         
,a.[01]                   
,a.[02]                   
,a.[03]
from #member1 a, #member1 b
WHERE a.member_id = b.member_id
and a.contact_address1_1<> b.contact_address1_1

[ck9663]

Use the ANSI way of joining tables. Check this out and use LEFT JOIN

Happy Coding!!!


~~ CK