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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -