duplicate record in view - 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 2008 Forums

Transact-SQL (2008)

duplicate record in view

Author

Topic

reload
Starting Member

7 Posts

Posted - 2010-05-19 : 00:09:52

hi All,

I have a cases in view that i created. many duplicate records in view. I want to do on such a view is to separate the same records from one column into five columns. The column number is certain because there are restrictions.

illustration ->

*> current view

hostname IP Address
PC A 010.010.010.010
PC A 010.010.006.030
PC A 010.010.009.022
PC B 010.010.010.023
PC B 010.010.009.111

*> view which is expected
hostname IP Addr1 IP Addr2 IP Addr3
PC A 010.010.010.010 010.010.006.030 010.010.009.022
PC B 010.010.010.023 010.010.009.111

i hope that you can help my cases.

thanks,

michael_aussie
Starting Member

15 Posts

Posted - 2010-05-19 : 01:02:07

You will need to give more information that that to get a reply.
I suggest you look at this link to see what information you should include.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-19 : 01:10:51

is there a max number of IP Addrs columns ?

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

reload
Starting Member

7 Posts

Posted - 2010-05-19 : 01:17:46

max column for IP Addrs is five columns.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-19 : 01:22:18

[code]
-- Sample TABLE
DECLARE @sample TABLE
(
hostname varchar(10),
IPAddr varchar(20)
)

-- Sample Data
INSERT INTO @sample
SELECT 'PC A', '010.010.010.010' UNION ALL
SELECT 'PC A', '010.010.006.030' UNION ALL
SELECT 'PC A', '010.010.009.022' UNION ALL
SELECT 'PC B', '010.010.010.023' UNION ALL
SELECT 'PC B', '010.010.009.111'

-- Query
; WITH
cte
AS
(
SELECT hostname, IPAddr,
row_no = row_number() OVER(PARTITION BY hostname ORDER BY IPAddr)
FROM @sample
)
SELECT hostname,
[1] AS [IP Addr1],
[2] AS [IP Addr2],
[3] AS [IP Addr3],
[4] AS [IP Addr4],
[5] AS [IP Addr5]
FROM cte
pivot
(
MAX(IPAddr)
FOR row_no IN ([1], [2], [3],[4], [5])
) p
/*
hostname IP Addr1 IP Addr2 IP Addr3 IP Addr4 IP Addr5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
PC A 010.010.006.030 010.010.009.022 010.010.010.010 NULL NULL
PC B 010.010.009.111 010.010.010.023 NULL NULL NULL

(2 row(s) affected)
*/
[/code]

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

reload
Starting Member

7 Posts

Posted - 2010-05-19 : 03:24:16

thanks khtan, you've given the problem-solving.

thank you so much

reload
Starting Member

7 Posts

Posted - 2010-05-19 : 04:21:14

Hi khtan,

I was a little trouble when there are several inter-related column. I attach the illustrations. I hope you can help me

current view
Host PhysicAddr IPAddr Gateway PrimaryDNS SecondaryDNS
bl-02 001F29E7CD92 000.000.000.000 NULL NULL NULL
bl-02 001F29E7CD94 010.000.019.082 010.000.019.254 010.000.014.006 NULL
fe-55a 0800270078A1 192.168.056.001 NULL NULL NULL
fe-55a D8D385A22580 000.000.000.000 NULL 010.000.014.006 NULL
fe-55a D8D385A22582 010.000.019.150 010.000.019.254 NULL NULL

thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-19 : 04:36:47

quote:
Originally posted by reload

Hi khtan,

I was a little trouble when there are several inter-related column. I attach the illustrations. I hope you can help me

current view
Host PhysicAddr IPAddr Gateway PrimaryDNS SecondaryDNS
bl-02 001F29E7CD92 000.000.000.000 NULL NULL NULL
bl-02 001F29E7CD94 010.000.019.082 010.000.019.254 010.000.014.006 NULL
fe-55a 0800270078A1 192.168.056.001 NULL NULL NULL
fe-55a D8D385A22580 000.000.000.000 NULL 010.000.014.006 NULL
fe-55a D8D385A22582 010.000.019.150 010.000.019.254 NULL NULL

thanks,

What is the expected result ?

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

reload
Starting Member

7 Posts

Posted - 2010-05-19 : 23:26:31

quote:
Originally posted by khtan

quote:
Originally posted by reload

Hi khtan,

I was a little trouble when there are several inter-related column. I attach the illustrations. I hope you can help me

current view
Host PhysicAddr IPAddr Gateway PrimaryDNS SecondaryDNS
bl-02 001F29E7CD92 000.000.000.000 NULL NULL NULL
bl-02 001F29E7CD94 010.000.019.082 010.000.019.254 010.000.014.006 NULL
fe-55a 0800270078A1 192.168.056.001 NULL NULL NULL
fe-55a D8D385A22580 000.000.000.000 NULL 010.000.014.006 NULL
fe-55a D8D385A22582 010.000.019.150 010.000.019.254 NULL NULL

thanks,

What is the expected result ?

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

I clarify again the intent of database view above.

*> The results i expect from a database view in the illustration above

Host-PhysicAddr1-PhysicAddr2-PhysicAddr3-PhysicAddr4-PhysicAddr5-IPAddr1-IPAddr2-IPAddr3-IPAddr4-IPAddr5-Gateway-PrimaryDNS-SecondaryDNS
bl-02-001F29E7CD92-001F29E7CD94-NULL-NULL-NULL-000.000.000.000-010.000.019.082-NULL-NULL-NULL-010.000.019.254-010.000.014.006-NULL
fe-55a-0800270078A1-D8D385A22580-D8D385A22582-NULL-NULL-192.168.056.001-000.000.000.000-010.000.019.150-NULL-NULL-010.000.019.254-010.000.014.006-NULL

But Field for the Gateway, PrimaryDNS, SecondaryDNS actually there is only one value while the value "NULL" thrown away.

thank you for your attention

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 02:03:16

[code]
; with cte
as
(
select *, row_no = row_number() over (partition by hostname order by IPAddr)
from yourtable
)
select hostname,
IPAddr1 = max(case when row_no = 1 then IPAddr end),
Gateway1 = max(case when row_no = 1 then Gateway end),
. . .
IPAddr2 = max(case when row_no = 2 then IPAddr end),
Gateway2 = max(case when row_no = 2 then Gateway end),
. . .
from cte
group by hostname
[/code]

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

reload
Starting Member

7 Posts

Posted - 2010-05-20 : 05:05:07

thanks a lot Khtan for your attention. you really help me for newbie. its working fine.

thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources