select statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

select statement

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-24 : 15:41:46

I have a data with following table structure. How do i get the like following output.

CREATE TABLE #tbl1
(
SName VARCHAR(32),
DName VARCHAR(32),
CName vARCHAR(32)
)

INSERT INTO #tbl1 VALUES('S1','AA','C1')
INSERT INTO #tbl1 VALUES('S1','BB','C2')
INSERT INTO #tbl1 VALUES('S1','BB','C3')
INSERT INTO #tbl1 VALUES('S1','BB','C4')
INSERT INTO #tbl1 VALUES('S1','DD','C13')

INSERT INTO #tbl1 VALUES('S2','CC','C5')
INSERT INTO #tbl1 VALUES('S2','AA','C6')
INSERT INTO #tbl1 VALUES('S2','AA','C7')
INSERT INTO #tbl1 VALUES('S2','BB','C8')

INSERT INTO #tbl1 VALUES('S3','BB','C9')
INSERT INTO #tbl1 VALUES('S3','DD','C10')
INSERT INTO #tbl1 VALUES('S3','CC','C11')
INSERT INTO #tbl1 VALUES('S3','AA','C12')
INSERT INTO #tbl1 VALUES('S3','AA','C14')

SELECT * FROM #tbl1

Drop table #tbl1

SName DName CName
S1 2 5
S2 3 4
S3 4 5

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 15:44:09

Can you explain the second Dname column in your output. How does it have values 2,3,4?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-24 : 15:47:07

I believe its a Distinct Count of Dname, although in his example it would be...

SName DName CName
S1 3 5

So

Select
SName,
Count(Distinct DName) as D_Count_DName,
Count(SName) as Count_SName
From #tbl1
Group by SName

Output:

SName	D_Count_DName	Count_SName
S1	3	5
S2	3	4
S3	4	5

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 15:51:01

quote:
I believe its a Distinct Count of Dname

Yeah..that sounds like the only reasonable answer.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-24 : 15:52:04

Sure, i have to count how many DName is there (group by DName, it has duplicates) where SName='S1'

e.g there are 3 different SName (S1,S2 and S3) so i have to count each SName has how many DName

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 15:57:57

So..DP978's solution works for u ...no?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-24 : 16:04:45

yes, it works. thanks

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-24 : 16:05:55

Not a problem.