some help pls.... - 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

General SQL Server Forums

New to SQL Server Programming

some help pls....

Author

Topic

sikharma13
Starting Member

44 Posts

Posted - 2014-11-24 : 19:34:10

i would like to know how to do this in sql..

table1
household_id|remark|month
000000000001|--2---|--7--
000000000002|--2---|--8--
000000000003|--1---|--7--
000000000003|--1---|--8--
000000000004|--0---|--7--
000000000005|--0---|--8--

my expected result is..

table2
household_id|remarks(0)|remarks(1)|remarks(2)|month(7)|month(8)
000000000001|----0-----|----0-----|----1-----|----1---|----0---
000000000002|----0-----|----0-----|----1-----|----0---|----1---
000000000003|----0-----|----2-----|----0-----|----1---|----1---
000000000004|----1-----|----0-----|----0-----|----1---|----0---
000000000005|----1-----|----0-----|----0-----|----0---|----1---

pls help..thanks in advance.. :)

VFP9.0 via MySQL 5.0

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 08:56:42

You want to do this in MySql? You should post your question in the MySql Forum

sikharma13
Starting Member

44 Posts

Posted - 2014-11-25 : 18:50:27

[quote]Originally posted by gbritton

You want to do this in MySql? You should post your question in the MySql Forum
[/quote

no sir, id like to know it using sql server.. thanks

VFP9.0 via MySQL 5.0

akibintel
Starting Member

7 Posts

Posted - 2014-11-25 : 23:32:33

I didnt get the question
u have table1 and table 2 wat exactly do u need plz elaborate

Thanks & Regards

sikharma13
Starting Member

44 Posts

Posted - 2014-11-26 : 00:50:46

quote:
Originally posted by akibintel

I didnt get the question
u have table1 and table 2 wat exactly do u need plz elaborate

Thanks & Regards

i have table 1, and my expected result is the table 2..
how to do that in sql?
in table 1 i have columns household_id/ remark/ and month
and in table 2 i have columns household_id/ remarks(0)/ remarks(1)/
remarks(2)/ month(7)/ month(8)..

VFP9.0 via MySQL 5.0

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 10:25:39

This works, though it would have been much easier if you didn't have all those dashes in your data:


DECLARE @table1 TABLE (household_id varchar(20), remark varchar(20),  month varchar(20))
INSERT INTO @table1
(
    household_id,
    remark,
    [month]
)
VALUES

('000000000001','--2---','--7--'),
('000000000002','--2---','--8--'),
('000000000003','--1---','--7--'),
('000000000003','--1---','--8--'),
('000000000004','--0---','--7--'),
('000000000005','--0---','--8--')

select household_id
	 , '----' + cast(count(case when remark = '--0---' then 1 end) as varchar(5)) + '-----' as [remarks(0)]
	 , '----' + cast(count(case when remark = '--1---' then 1 end) as varchar(5)) + '-----' as [remarks(1)]
	 , '----' + cast(count(case when remark = '--2---' then 1 end) as varchar(5)) + '-----' as [remarks(2)]
	 , '----' + cast(count(case when [month] = '--7--' then 1 end) as varchar(5)) + '---' as [month(7)]
	 , '----' + cast(count(case when [month] = '--8--' then 1 end) as varchar(5)) + '---' as [month(8)]
from @table1
group by household_id

Also, your signature says MySql, hence the confusion about the platform.

sikharma13
Starting Member

44 Posts

Posted - 2014-11-26 : 18:58:00

quote:

Originally posted by gbritton

This works, though it would have been much easier if you didn't have all those dashes in your data:


DECLARE @table1 TABLE (household_id varchar(20), remark varchar(20),  month varchar(20))
INSERT INTO @table1
(
    household_id,
    remark,
    [month]
)
VALUES

('000000000001','--2---','--7--'),
('000000000002','--2---','--8--'),
('000000000003','--1---','--7--'),
('000000000003','--1---','--8--'),
('000000000004','--0---','--7--'),
('000000000005','--0---','--8--')

select household_id
	 , '----' + cast(count(case when remark = '--0---' then 1 end) as varchar(5)) + '-----' as [remarks(0)]
	 , '----' + cast(count(case when remark = '--1---' then 1 end) as varchar(5)) + '-----' as [remarks(1)]
	 , '----' + cast(count(case when remark = '--2---' then 1 end) as varchar(5)) + '-----' as [remarks(2)]
	 , '----' + cast(count(case when [month] = '--7--' then 1 end) as varchar(5)) + '---' as [month(7)]
	 , '----' + cast(count(case when [month] = '--8--' then 1 end) as varchar(5)) + '---' as [month(8)]
from @table1
group by household_id

Also, your signature says MySql, hence the confusion about the platform.

thanks sir for the reply.. i used dashes just to align my data.. anyways thanks,,

VFP9.0 via MySQL 5.0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources