Dear friends;
I have a CROSS table in ACCESS that I want to export to SQL 2005. The query is:
TRANSFORM Count(GD_PROFILE.cnRow) AS CountOfcnRow
SELECT GD_PROFILE.ID
FROM GD_PROFILE
GROUP BY GD_PROFILE.ID
PIVOT GD_PROFILE.ID_APLIC;
The RESULT is:
ID
20
22
77
122
141
179
211
327
334
370
388
405
524
584
645
687
826
865
914
CN1
1
1
1
1
1
CN2
1
1
1
1
CN3
1
1
1
1
CN4
1
1
1
1
1
CN5
1
1
1
1
CN8
1
1
1
1
1
1
1
1
1
1
1
CN14
1
1
1
1
1
1
1
1
CN15
1
1
1
1
1
1
1
1
1
1
CN22
1
1
1
1
1
1
1
1
CN29
1
1
1
1
1
1
1
CN36
1
1
1
I want to create a query in SQL Server that give me rows from the cross query that are equal. For example, the row 1 and 4 are equal. Has the same values in each row. How can I make it programatically?
The Cross is a simple cross query, and the goal is return this final result:
Profile 1 - ID CN1, ID CN4
Profile 2 - ID CN2, ID CN3, ID CN5
Profile 3 - ID CN8
Profile 4 - ID CN14
Profile 5 - ID CN15
Profile 6 - ID CN22
Profile 7 - ID CN29
Profile 8 - ID CN36
Could you HELP me?
THANKS!!!!
HELP me please!!!!
I need it soon!!
Thanks!!!!!!!
|||
--For SQL Server 2005:
SELECT ROW_Number() OVER(ORDER BY (pvt.[1] + coalesce(', ' + pvt.[2], '') + coalesce(', ' + pvt.[3], ''))) AS Profile,
pvt.[1] + coalesce(', ' + pvt.[2], '') + coalesce(', ' + pvt.[3], '') as merged
FROM(SELECT ID, ROW_Number() OVER (PARTITION BY coalesce (',' + [F2] , '-') + + coalesce(',' + [F3], '-') + coalesce(',' + [F4], '-' ) + coalesce(',' + [F5], '-') + coalesce(',' + [F6], '-' ) + + coalesce (',' + [F7] , '-') + coalesce(',' + [F8], '-') +coalesce(',' + [F9], '-') + coalesce(',' + [F10], '-' ) + + coalesce (',' + [F11] , '-') + coalesce(',' + [F12], '-') + coalesce(',' + [F13], '-') + coalesce(',' + [F14], '-' ) + + coalesce (',' + [F15] , '-') + coalesce(',' + [F16], '-') +coalesce(',' + [F17], '-') + coalesce(',' + [F18], '-') + + coalesce( ',' + [F19], '-') + coalesce (',' + [F20] , '-') ORDER BY ID) AS rowNum_ID,
coalesce(',' + [F2], '-') + + coalesce( ',' + [F3], '-') + coalesce (',' + [F4] , '-') + coalesce(',' + [F5], '-') + coalesce(',' + [F6], '-' ) + + coalesce (',' + [F7] , '-') + coalesce(',' + [F8], '-') + coalesce(',' + [F9], '-') + coalesce(',' + [F10], '-' ) + + coalesce (',' + [F11] , '-') + coalesce(',' + [F12], '-') + coalesce(',' + [F13], '-') + coalesce(',' + [F14], '-' ) + + coalesce (',' + [F15] , '-') + coalesce(',' + [F16], '-') + coalesce(',' + [F17], '-') + coalesce(',' + [F18], '-') + + coalesce( ',' + [F19], '-') + coalesce (',' + [F20] , '-') AS newlabel
FROM yourTable) as t1
pivot (min(ID) for rowNum_ID in ([1], [2], [3]))AS pvt
ORDER BY pvt.[1] + coalesce(', ' + pvt.[2], '') + coalesce(', ' + pvt.[3], '')
CREATE TABLE yourTable(
[ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F5] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F6] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F7] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F8] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F9] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F10] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F11] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F12] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F13] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F14] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F15] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F16] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F17] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F18] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F19] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[F20] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN1',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN2',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN3',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN4',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN5',NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN8','1','1',NULL,'1','1',NULL,NULL,NULL,NULL,'1','1',NULL,NULL,'1','1','1','1','1',NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN14','1','1',NULL,'1','1',NULL,NULL,NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN15','1','1','1','1','1',NULL,'1',NULL,NULL,'1','1',NULL,NULL,'1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN22',NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,'1','1',NULL,'1','1','1',NULL,NULL,'1',NULL,NULL)
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN29','1','1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,'1',NULL,'1',NULL,NULL,'1',NULL,'1')
INSERT INTO [yourTable] ([ID],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9],[F10],[F11],[F12],[F13],[F14],[F15],[F16],[F17],[F18],[F19],[F20])VALUES('CN36',NULL,NULL,NULL,'1',NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,NULL)
|||Dear Friend!
First of all I want to thank your help.
The Grid above is a result of a CROSS query based in this table:
What I want is to check the CN (Computernames) that have the same profile. Example, I know that CN1 and CN4 has the same aplications, the profile one.
For each CN I need to know if the profile for this CN already exists, if true insert the reference in Computername Table, and if false, create new Profile in table Profile and the aplicationIDs in Profile_AplicID Table.
COULD YOU HELP ME?!!
THANKS FRIEND!!!
|||
Anyone need more help to understand my problem?
Thanks!!!
|||Anyonne there?
No comments:
Post a Comment