Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Using case or any other way

Author  Topic 

akalehzan
Starting Member

21 Posts
Posted - 2007-12-11 : 19:53:48
Hi All,

I have the following table with some data:

snum:	E_IQ:	_DATE:   	_study
165	103	2/21/1999	TravelT1
165	107	7/13/2000	FraXimagingT1
1364	123	5/20/1998	TravelT1 FraXimagingT1
1364	116	11/29/2001	FraXimagingT2
1364	125	7/23/2004	TravelT2
1406	65	10/31/1999	TravelT1
3841	68	6/17/2000	TravelT1
3841	46	11/20/2004	TravelT2
3845	72	6/18/2000	TravelT1
4149	47	9/18/2000	TravelT1
4149	48	6/8/2004	TravelT2
4176	66	9/29/2000	TravelT1
4176	58	11/3/2004	TravelT2





Is that possible to have a report output having all the columns side by side based on the order that is showing below?


subj_num
165
1364
1406
3841
3845
4149
4176

_DATE
2/21/1999
5/20/1998
10/31/1999
6/17/2000
6/18/2000
9/18/2000
9/29/2000

E_IQ
103
123
65
68
72
47
66


_study
TravelT1
TravelT1 
TravelT1
TravelT1
TravelT1
TravelT1
TravelT1


_DATE
null
7/23/2004
null
11/20/2004
null
6/8/2004
11/3/2004

E_IQ
Null
125
Null
46
Null
48
58

_study
null
TravelT2
null
TravelT2
null
TravelT2
TravelT2

_DATE
5/10/2004
null
12/9/2004
null
2/11/2004
null
null

E_IQ
98
null
67
null
79
null


_study
TravelT2
null
TravelT2
null
TravelT2
null
null





I use the following SQL case scrip:

select Snum,  E_IQ, _DATE
  ,case when _study like '%t1' then _study end as _study_T1
  ,case when _study like '%t2' then _study end as _study_T2

from BEH_WISC_III
                 where snum in (165, 1364, 1406, 3841, 3845, 4149, 4176)
                 and (_study like '%T1' or  _study like  '%T2')




But the out put is like the following:

snum	E_IQ	_DATE	        _study
165	103	2/21/1999	TravelT1
165	107	7/13/2000	FraXimagingT1
1364	123	5/20/1998	TravelT1 FraXimagingT1
1364	116	11/29/2001	FraXimagingT2
1364	125	7/23/2004	TravelT2
1406	65	10/31/1999	TravelT1
3841	68	6/17/2000	TravelT1
3841	46	11/20/2004	TravelT2
3845	72	6/18/2000	TravelT1
4149	47	9/18/2000	TravelT1
4149	48	6/8/2004	TravelT2
4176	66	9/29/2000	TravelT1
4176	58	11/3/2004	TravelT2




Thanks for any help.

Abrahim

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-12-11 : 20:37:24
sorry, bit confuse. What is your result that you required ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-11 : 22:53:58
Dear Kh,

We like to see all the columns side by side based on the order that is showed below:
[code]
Snum
165
1364
1406
3841
3845
4149
4176

_DATE
2/21/1999
5/20/1998
10/31/1999
6/17/2000
6/18/2000
9/18/2000

E_IQ
103
123
65
68
72
47
66


_study
TravelT1
TravelT1
TravelT1
TravelT1
TravelT1
TravelT1
TravelT1


_DATE
null
7/23/2004
null
11/20/2004
null
6/8/2004
11/3/2004

E_IQ
Null
125
Null
46
Null
48
58

_study
null
TravelT2
null
TravelT2
null
TravelT2
TravelT2

_DATE
5/10/2004
null
12/9/2004
null
2/11/2004
null
null

E_IQ
98
null
67
null
79
null


_study
TravelT2
null
TravelT2
null
TravelT2
null
null

[/cod]
Somehow I couldnt lineup all the columns in the above example side by side,
So, here is the order:

Snum : _DATE : E_IQ: _study: _DATE: E_IQ: _study: _DATE: E_IQ: _study:E_IQ: _study:



Thanks in advance,

Abrahim
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-12-11 : 23:01:44
sorry, i am still totally lost.

What do you mean by "side by side" ?
the result you posted is all in rows.

You want the Snum all in rows follow by E_IQ also in rows ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-12 : 00:03:06
I would like the columns to present from left to right across the page with the data beneath each column heading.

For example:




Column 1      Column 2      Column 3      Column 4
data          data          data          data 
data          data          data          data




Does that make sence?

Please let me know.

Thanks,

Abrahim
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2007-12-12 : 12:38:31
So what you really want is one row per SNUM?
Like this for example? 
SNum e_iq Date      study                  e_iq Date       study         e_iq Date       Study 
165  103  2/21/1999 TravelT1               107  07/13/2000 FraXimagingT1 NULL NULL       NULL
1364 123  5/20/1998 TravelT1 FraXimagingT1 116  11/29/2001 FraXimagingT2 125  07/23/2004 TravelT2
--etc..
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-13 : 17:26:56
Hi All,

Someone


Someone suggested me to use table alias and inner quarry, so I have came up with this code but Im getting error:



SELECT b2.subj_num AS S_NUM, WSC_FULL_SCALE_IQ AS T1_SCALE_IQ , WSC_which_study
AS T1_study,
(SELECT TOP 1 WSC_FULL_SCALE_IQ FROM BEH_WISC_III where subj_num = b2.subj_num)
AS T2_WSC_DATE,
(SELECT TOP 1 WSC_which_study FROM BEH_WISC_III where subj_num  = b2.subj_num)
AS T2_which_study FROM  BEH_WISC_III b2
where subi_num in (1364, 165, 1406,3841, 4149, 4176)


Here is the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ''.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ''.


Thanks for any help,
Abrahim
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-15 : 02:48:12
Ok,

I know everybody is getting sick of this, but I got the script to work but it is not doing what I was expecting:


select distinct b.subj_num as SUBJ_NUM, WSC_FULL_SCALE_IQ as IQ, 
(select top 1 WSC_which_study from BEH_WISC_III where subj_num = b.subj_num
and WSC_which_study like 'TravelT1')
	AS Study1,
	
(select top 1 WSC_which_study from BEH_WISC_III where subj_num = b.subj_num
and WSC_which_study like 'TravelT2')
	AS Study2,
	
(select top 1 WSC_which_study from BEH_WISC_III where subj_num = b.subj_num
and WSC_which_study like 'TravelT1 FraXimagingT1')
	AS StudyFXT1,
	
	(select top 1 WSC_which_study from BEH_WISC_III where subj_num = b.subj_num
and WSC_which_study like 'FraXimagingT2')
	AS StudyFXT2		
  from BEH_WISC_III b where subj_num  in (1364, 165, 1406,3841, 4149, 4176)


I wish there was a way to attach an Excel spreadsheet to this post whit the example of the report out put that Im wishing for.

Abrahim
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-12-15 : 02:54:18
quote:
I wish there was a way to attach an Excel spreadsheet to this post whit the example of the report out put that Im wishing for.

Why don't you post the expected output here using the [ code ] [ /code ] (without spaces in between the []), like what Lamprey did ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-15 : 17:43:04
Ok,
here is the expected output:


subj_num	WISC_III.WSC_DATE	WISC_III.WSC_FULL_SCALE_IQ	WISC_III.WSC_which_study	WISC_III_1.WSC_DATE	WISC_III_1.WSC_FULL_SCALE_IQ	WISC_III_1.WSC_which_study	WAS_DATE	WAS_FULL_SCALE_IQ	WAS_which_study
165	2/21/1999	103	TravelT1				5/10/2004	98	TravelT2
1364	5/20/1998	123	TravelT1 	7/23/2004	125	TravelT2			
1406	10/31/1999	65	TravelT1				12/9/2004	67	TravelT2
3841	6/17/2000	68	TravelT1	11/20/2004	46	TravelT2			
3845	6/18/2000	72	TravelT1				2/11/2004	79	TravelT2
4149	9/18/2000	47	TravelT1	6/8/2004	48	TravelT2			
4176	9/29/2000	66	TravelT1	11/3/2004	58	TravelT2



If you paste all the above output and three tables into Excel sheet, then you should see the columns clearly.

Here are the tables:

ID table with subj_num:
Subj_num
165
1364
1406
3841
3845
4149
4176
here is the BEH_WISC_III table:
subj_num	WSC_DATE	WSC_FULL_SCALE_IQ	WSC_which_study
165	2/21/1999	103	TravelT1
165	7/13/2000	107	FraXimagingT1
1364	5/20/1998	123	TravelT1 FraXimagingT1
1364	11/29/2001	116	FraXimagingT2
1364	7/23/2004	125	TravelT2
1406	10/31/1999	65	TravelT1
3841	6/17/2000	68	TravelT1
3841	11/20/2004	46	TravelT2
3845	6/18/2000	72	TravelT1
4149	9/18/2000	47	TravelT1
4149	6/8/2004	48	TravelT2
4176	9/29/2000	66	TravelT1
4176	11/3/2004	58	TravelT2


here is BEH_WAIS_III table:
subj_num	WAS_DATE	WAS_FULL_SCALE_IQ
165	5/10/2004	98
1406	12/9/2004	67
3845	2/11/2004	79





Thanks in advance,

Abrahim
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-12-16 : 08:46:20
Your expected output is still mis-aligned. I could only guess this is what you want ?


DECLARE @ID TABLE
(
	subj_num int
)
INSERT INTO @ID
SELECT	165  UNION ALL
SELECT	1364 UNION ALL
SELECT	1406 UNION ALL
SELECT	3841 UNION ALL
SELECT	3845 UNION ALL
SELECT	4149 UNION ALL
SELECT	4176

DECLARE @BEH_WISC_III TABLE
(
	subj_num		int,
	WSC_DATE		datetime,
	WSC_FULL_SCALE_IQ	int,
	WSC_which_study		varchar(30)
)
INSERT INTO @BEH_WISC_III
SELECT	165,	'2/21/1999',	103,	'TravelT1'		UNION ALL
SELECT	165,	'7/13/2000',	107,	'FraXimagingT1'		UNION ALL
SELECT	1364,	'5/20/1998',	123,	'TravelT1 FraXimagingT1' UNION ALL
SELECT	1364,	'11/29/2001',	116,	'FraXimagingT2'		UNION ALL
SELECT	1364,	'7/23/2004',	125,	'TravelT2'		UNION ALL
SELECT	1406,	'10/31/1999',	65,	'TravelT1'		UNION ALL
SELECT	3841,	'6/17/2000',	68,	'TravelT1'		UNION ALL
SELECT	3841,	'11/20/2004',	46,	'TravelT2'		UNION ALL
SELECT	3845,	'6/18/2000',	72,	'TravelT1'		UNION ALL
SELECT	4149,	'9/18/2000',	47,	'TravelT1'		UNION ALL
SELECT	4149,	'6/8/2004',	48,	'TravelT2'		UNION ALL
SELECT	4176,	'9/29/2000',	66,	'TravelT1'		UNION ALL
SELECT	4176,	'11/3/2004',	58,	'TravelT2'


DECLARE @BEH_WAIS_III TABLE
(
	subj_num		int,
	WAS_DATE		datetime,
	WAS_FULL_SCALE_IQ	int
)

INSERT INTO @BEH_WAIS_III
SELECT	165,	'5/10/2004',	98 UNION ALL
SELECT	1406,	'12/9/2004',	67 UNION ALL
SELECT	3845,	'2/11/2004',	79

SELECT	[subj_num]			= i.subj_num,
	[WISC_III.WSC_DATE] 		= MAX(CASE WHEN seq_no = 1 THEN i.WSC_DATE END),
	[WISC_III.WSC_FULL_SCALE_IQ]	= MAX(CASE WHEN seq_no = 1 THEN i.WSC_FULL_SCALE_IQ END),
	[WISC_III.WSC_which_study]	= MAX(CASE WHEN seq_no = 1 THEN i.WSC_which_study END),
	[WISC_III_1.WSC_DATE] 		= MAX(CASE WHEN seq_no = 2 THEN i.WSC_DATE END),
	[WISC_III_1.WSC_FULL_SCALE_IQ]	= MAX(CASE WHEN seq_no = 2 THEN i.WSC_FULL_SCALE_IQ END),
	[WISC_III_1.WSC_which_study]	= MAX(CASE WHEN seq_no = 2 THEN i.WSC_which_study END),
	[WAS_DATE]			= MAX(a.WAS_DATE),
	[WAS_FULL_SCALE_IQ]		= MAX(a.WAS_FULL_SCALE_IQ)
FROM
(
	SELECT	subj_num, WSC_DATE, WSC_FULL_SCALE_IQ, WSC_which_study,
		seq_no = (SELECT COUNT(*) FROM @BEH_WISC_III x WHERE x.subj_num = i.subj_num AND x.WSC_DATE <= i.WSC_DATE)
	FROM	@BEH_WISC_III i
) i
left JOIN @BEH_WAIS_III a	ON	i.subj_num	= a.subj_num
GROUP BY i.subj_num
ORDER BY i.subj_num


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-16 : 21:48:56
Hi Kh,

Almost there, but I just realized that the information that I posted about
BEH_WAIS_III table was not complete, so here is the complete info:
About BEH_WAIS_III table:

WAS_DATE	WAS_FULL_SCALE_IQ	WAS_which_study
05/10/2004	98	TravelT2
12/09/2004	67	TravelT2
02/11/2004	79	TravelT2



Sorry about that.
Thanks in advance,


Abrahim
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-12-16 : 21:51:16
So is the query does what you want ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-12-16 : 22:33:36
This could go on for weeks...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

akalehzan
Starting Member

21 Posts
Posted - 2007-12-16 : 22:49:58
Kh,

Yes indeed, thats what we ware looking for.

Regards,

Abrahim
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site