Need Help in extracting the result

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

Need Help in extracting the result

Author

Topic

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-27 : 05:13:36

I have a table having multiple fields but I need to extract data based on the two fields.

Field1 Field2
1 | 123
1 | 213
2 | 123
2 | 3654
2 | 4758

I want the result with 2 cases
1.) distinct in Field 1 but multiple in field 2
expected result:
Field1 column1 column2 column3
1 | 123 | 213
2 | 123 | 3654 | 4758

2.) multiple in field 1 but distinct in field 2

Expected Result :
Field 2 | Column1 | Column2
123 | 1 | 2

there may be more columns added according to the data available.

Please help me

pankajrocks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-27 : 05:50:23

[code]
create table #tbl
(
Field1 int,
Field2 int
)

insert into #tbl select 1 , 123
insert into #tbl select 1 , 213
insert into #tbl select 2 , 123
insert into #tbl select 2 , 3654
insert into #tbl select 2 , 4758

declare @sql varchar(max),
@col varchar(max),
@cols int

-- case 1
select @cols = max(cnt)
from
(
select Field1, cnt = count(distinct Field2)
from #tbl
group by Field1
) d

select @col = null
; with num as
(
select n = 1
union all
select n = n + 1
from num
where n < @cols
)
select @col = isnull(@col + ',', '') + quotename(convert(varchar(10), n))
from num

select @sql = N'
select *
from (
select Field1, Field2, ColNo = row_number() over (partition by Field1 order by Field2)
from #tbl
) t
pivot
(
max(Field2)
for ColNo in (' + @col + ')
) p'

print @sql
exec (@sql)

-- Case 2
select @cols = max(cnt)
from
(
select Field2, cnt = count(distinct Field1)
from #tbl
group by Field2
) d

select @col = null
; with num as
(
select n = 1
union all
select n = n + 1
from num
where n < @cols
)
select @col = isnull(@col + ',', '') + quotename(convert(varchar(10), n))
from num

select @sql = N'
select *
from (
select Field1, Field2, ColNo = row_number() over (partition by Field2 order by Field1)
from #tbl
) t
pivot
(
max(Field1)
for ColNo in (' + @col + ')
) p'

print @sql
exec (@sql)

drop table #tbl[/code]

KH
[spoiler]Time is always against us[/spoiler]

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-27 : 06:14:46

Thanks for sharing Mr.Khtan but can you please describe the query

pankajrocks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 11:53:10

Panka,

That is called a dynamic pivot. Here is a link that might help. If not, just do a search and you should find a bunch of links:

http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-27 : 21:26:40

I also want to use the where clause in the query to find the filtered report. Please help

pankajrocks

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-28 : 02:27:21

Now here is some change in the database in field 2 I've repeatative value against the same field 1
like :
Field1 Field2
1 | 123
1 | 234
1 | 123
2 | 567
2 | 23145
2 | 567
3 | 123
3 | 547

the conditions are same. So please help me
to extract the report like
Case 1:

Field1 col1 col2
1 | 123 | 234
2 | 567 | 23145
3 | 123 | 547

Case 2:

Field2 Col1 col2 col3
123 | 1 | 3
234 | 1
567 | 2
23145 | 2
547 | 3

pankajrocks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-28 : 03:44:29

change row_number() to dense_rank()

KH
[spoiler]Time is always against us[/spoiler]

pankaj2910
Starting Member

31 Posts

Posted - 2014-05-28 : 05:24:20

hi Khtan,

Thanks for giving solution.
For case 2 it gives error

Msg 530, Level 16, State 1, Line 13
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

pankajrocks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources