problem in inserting date in sql server 7 through insert query

hello myself avinash
i am developing on application having vb 6 as front end and sql server 7
as back end.
when i use insert query to insert data in table then the date value of
that query is going as 01/01/1900
my query is as follows

StrSql = "Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
2)
& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
2)
& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
2)
+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
0)
& ")"

and its output is

Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)

in above query though i used cdate to voucherdate value still it save in
database as 01/01/1900 though here it shows right date
plz help me its a very big issue for me & i really just fed of this
problemYou might try running a Profiler trace to capture the actual statement
executed by SQL Server and check for any triggers that might change the
value. Note that SQL Server will interpret an empty string as 1900-01-01.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:9c11bb7aacaeeddcf230738468fd3b72@.localhost.ta lkaboutdatabases.com...
> hello myself avinash
> i am developing on application having vb 6 as front end and sql server 7
> as back end.
> when i use insert query to insert data in table then the date value of
> that query is going as 01/01/1900
> my query is as follows
> StrSql = "Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
> StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
> txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
> "'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
> 2)
> & "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
> val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
> 2)
> & ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
> vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
> vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
> 2)
> + val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
> IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
> 0)
> & ")"
> and its output is
> Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
> Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)
> in above query though i used cdate to voucherdate value still it save in
> database as 01/01/1900 though here it shows right date
> plz help me its a very big issue for me & i really just fed of this
> problem|||hi avinash
i have come across such problems frequently. i would advise u
to set the date format within dtpicker control u are using. Check
properties for the control and set the format to custom. then set the
mask.
thats it.

Regards
Debashishsql

Problem in inner join ?

Hai ,

select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

The output of above query is

emp_id count1

EMP10041 8
EMP10058 8
EMP10059 6
EMP10008 6
EMP10012 3
EMP10018 3
EMP10039 3
EMP10001 2

I have another table as user_table

user_id user_name

EMP10001 Raja
EMP10039 Ram
EMP10018 Ravi

etc

now i am writing a inner join as follows

select y.user_name, x.count1 from [select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc] x inner join [user_table] y on x.emp_id = y.user_id

I want to get the below format

Jambu 8
Elangovan 8
Ravi 6
Anitha 6
Ram 3

but i am getting the below error :

Invalid object name 'select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

how to solve this

remove the [ ] & order by clasue on the sub query. Sub query should be enclosed with ( ).

Code Snippet

select

y.user_name

, x.count1

from

(select emp_id,count(emp_id) as count1 from poll_data group by emp_id) x

inner join [user_table] y on x.emp_id = y.user_id

|||

You need to enclose the derived table in parentheses -NOT square brackets.

Code Snippet

SELECT
u.User_Name,
p.Count1
FROM (SELECT
Emp_ID,
Count1 = count(Emp_ID)
FROM Poll_Data
GROUP BY Emp_ID
) p
INNER JOIN User_Table u
ON p.Emp_ID = u.User_ID

problem in export of csv file into sql server

I am trying to export a CSV file into sql server. I wrote a query to perform select fields from csv file using OPENROWSET. I am getting all the values in a single column, but I need to get in three different columns. Any can body can help me



The query is as follows

Select * from OpenRowset ('MSDASQL','Driver= {Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=” Directory name” \;Extended properties=''ColNameHeader=True; Format=Delimited;''','select * from Sample.csv')



Sample.csv

col1, col2, col3 ---- all the values are in single cell of excel csv file
1, a, abc
2, sfasf, sdgagas



Output of query is

col1,col2,col3 --- all the values are coming in one column and with comma between them
1,a,abc
2,sfasf,sdgagas

can any body help me where i am going wrong?

Thanks in advance
suryamight be easier to just import the entire file to a staging table and the work with it from there.|||would u like to give me some sample code|||Doh - something wrong with my pc, please ignore 2 duplicate answers and refer the top one.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.
See this http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx fyi.

problem in executing package in production environment

hi all!

This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.

when i try to execute through cmd prompt by setting \conn <new database connection string> it fails.

Is package configuration is the only solution. how can i change conn string depending on different server?

Any help would be appreciated.

Thanks,

Jas

As far as my knowledge, package configuration is the only one solution.|||

Package configurations will work but you'll need to reference the configuration at runtime. You can also use the /SET switch from dtexec.exe to set a property dynamically at runtime. If you're trying to set a connection string though, you can set it with the /Connection swtich. Try doing this from DtsExecUI.exe first to see if you have better luck then grab the command line from the last page down. Hope this helps!

Brian

|||

i am currently trying to set the variable with the /set and give connection string as /conn but when i do it another environment and change the conn string it does not work. Now thats my problem. Now i am trying through package configuration but that too i have some issues. I don't want to use environment variable and cannot use parent pacakge varibale for this particular pacakge cos it is the main parent pacakge. I want to use registry key but if i give the value other than current user. it does not work. I am trying to use the HKEY_local machine /software / .../ .../ value. How doi set the registry key for this in pacakge configuration. And i also tried the config file. I works well through indirect method only in another environment where i need to set the environment variable to hold the path of the config file.

Is there any suggestions?

Thanks,

JAs

sql

problem in executing package in production environment

hi all!

This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.

when i try to execute through cmd prompt by setting \conn <new database connection string> it fails.

Is package configuration is the only solution. how can i change conn string depending on different server?

Any help would be appreciated.

Thanks,

Jas

As far as my knowledge, package configuration is the only one solution.|||

Package configurations will work but you'll need to reference the configuration at runtime. You can also use the /SET switch from dtexec.exe to set a property dynamically at runtime. If you're trying to set a connection string though, you can set it with the /Connection swtich. Try doing this from DtsExecUI.exe first to see if you have better luck then grab the command line from the last page down. Hope this helps!

Brian

|||

i am currently trying to set the variable with the /set and give connection string as /conn but when i do it another environment and change the conn string it does not work. Now thats my problem. Now i am trying through package configuration but that too i have some issues. I don't want to use environment variable and cannot use parent pacakge varibale for this particular pacakge cos it is the main parent pacakge. I want to use registry key but if i give the value other than current user. it does not work. I am trying to use the HKEY_local machine /software / .../ .../ value. How doi set the registry key for this in pacakge configuration. And i also tried the config file. I works well through indirect method only in another environment where i need to set the environment variable to hold the path of the config file.

Is there any suggestions?

Thanks,

JAs

Problem in DB

Trying to run the following query
SELECT COUNT(*) AS Expr1
FROM TableA INNER JOIN
TableB ON TableA.Id = TableB.Id
WHERE (TableB.EmployeeId = 15)
I get the error
"Attempt to fetch logical page 1:0 in database XXXXXXX belongs to object
'ALLOCATION', not to object 'TableA'
Also when I run the query
SELECT COUNT(*) AS Expr1
FROM TableA INNER JOIN
TableB ON TableA.Id = TableB.Id
I get the error
"Could not open FCB for invalid file ID 0 in database XXXXXXX"
It seems that there is something wrong with the specific DB
Any suggestions ?
Thanks
YannisYannis
Run DBCC CHECKDB to fix some errors in the database
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OlBD9QMZEHA.1480@.TK2MSFTNGP10.phx.gbl...
> Trying to run the following query
> SELECT COUNT(*) AS Expr1
> FROM TableA INNER JOIN
> TableB ON TableA.Id = TableB.Id
> WHERE (TableB.EmployeeId = 15)
> I get the error
> "Attempt to fetch logical page 1:0 in database XXXXXXX belongs to object
> 'ALLOCATION', not to object 'TableA'
> Also when I run the query
> SELECT COUNT(*) AS Expr1
> FROM TableA INNER JOIN
> TableB ON TableA.Id = TableB.Id
> I get the error
> "Could not open FCB for invalid file ID 0 in database XXXXXXX"
> It seems that there is something wrong with the specific DB
> Any suggestions ?
> Thanks
> Yannis
>

Problem in creating endpoint

Hello Everybody,

I dont know whether this is right forum to post these type of query or not. but please help me in solving this problem.

I am creating Endpoint in sql server 2005.

but in very first line its giving error.

plz let me know whats wrong with this code.

USE [Northwind]

GO

CREATE ENDPOINT customer_endpoint

STATE=STARTED

AS HTTP (

PATH='/sql',

AUTHENTICATION= (INTEGRATED),

PORTS = (CLEAR),

SITE = '*'

)

FOR SOAP (

WEBMETHOD 'http://tempUri.org'.'GetCustomerInfo'

(

NAME='Northwind.dbo.upGetCustomer', FORMAT=ROWSETS_ONLY,

schema=STANDARD

),

WSDL=DEFAULT,

BATCHES=ENABLED,

DATABASE='Northwind'

)

GO

and the Error i am getting is:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'ENDPOINT'.

Please Help me ASAP.

Regards,

Vineet

I just did a cut and paste of the DDL and it worked for me. I ran it against SQL2K5 RTM build. The Northwind database is not available by default with SQL2K5, even then it should merely fail by saying "Database not found".

Problem in creating endpoint

Hello Everybody,

I dont know whether this is right forum to post these type of query or not. but please help me in solving this problem.

I am creating Endpoint in sql server 2005.

but in very first line its giving error.

plz let me know whats wrong with this code.

USE [Northwind]

GO

CREATE ENDPOINT customer_endpoint

STATE=STARTED

AS HTTP (

PATH='/sql',

AUTHENTICATION= (INTEGRATED),

PORTS = (CLEAR),

SITE = '*'

)

FOR SOAP (

WEBMETHOD 'http://tempUri.org'.'GetCustomerInfo'

(

NAME='Northwind.dbo.upGetCustomer', FORMAT=ROWSETS_ONLY,

schema=STANDARD

),

WSDL=DEFAULT,

BATCHES=ENABLED,

DATABASE='Northwind'

)

GO

and the Error i am getting is:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'ENDPOINT'.

Please Help me ASAP.

Regards,

Vineet

I just did a cut and paste of the DDL and it worked for me. I ran it against SQL2K5 RTM build. The Northwind database is not available by default with SQL2K5, even then it should merely fail by saying "Database not found".

sql

Problem in creating Asymmetric keys

I am a novice to the SQL server. I am trying to create Asymmetric key using the query

CREATE ASYMMETRIC KEY PacificSales19 AUTHORIZATION dbo

FROM FILE = ' C:\temp\temp1.snk'

ENCRYPTION BY PASSWORD = 'ABC123!@.#$';

GO

But I alwys get the follwing error

The certificate, asymmetric key, or private key file does not exist or has invalid format.

Can anyone please guide me as to how to go ablout creating the ASYMMETRIC KEY FROM FILE.

Thanks and regards

The statement you are trying is correct - this is how you can create an asymmetric key from a file.

You should check that the specified path is correct and that the file is not corrupted. How did you generate that file?

Thanks
Laurentiu

Problem in connecting 2 databases with sql Server 2005 Express

I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?

Connection String #1 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Main.mdf;Data Source=Steve_Laptop\sqlexpress

Connection String #2 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Items.mdf;Data Source=Steve_Laptop\sqlexpress

Sql Query: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.PFMS'

Sql Query#2: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.dbo.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.dbo.PFMS'.

I am running the queries directly from the 2005 Mgt window to take as many variables out of the equasion. I get the same error in Visual basic

How am I supposed to reference a join of 2 databases? Any suggestions

I did not try that yet, but I think the cross join between user instances is not possible, you either have to do an in-memory join in .NET or attach the databases to the SQL Server Express instance and then join them using the three part name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem in a QUERY with COUNT

CREATE PROCEDURE [dbo].[GD_SP_HARDWARE_MONITOR_COUNT]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

AS

DECLARE @.NrMon int

BEGIN

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS Total

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

END

DEAR FRIENDS,

HOW CAN I MULTIPLICATE THE VALUE FROM UNIDADES AND TOTAL?

Current output:

Unidades

/Total

TFT 17

417

/24,35

TFT 15

3254

/22,08

GOAL:

Unidades

/Total

TFT 17

417

/24,35

/10153,95

TFT 15

3254

/22,08

/71848,32

THANKS

Wrap your query in an outer query:

SELECT Item, Unidades, Total, Unidades*Total AS SomethingNew
FROM
(
Here comes your existing query
) SubQUery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

THANKS!!!!!!

FANTASTIC!!!!

|||

Another question:
I made a UNION with 2 querys :

ALTER PROCEDURE [dbo].[GD_SP_FACTURA]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

AS

BEGIN

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.ModeloPC_Tipo.MOD_Nome AS Item, COUNT(*) AS Unidades, dbo.ModeloPC_Tipo.MOD_CustoUnit AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.ModeloPC ON dbo.HARDWARE.MODELO_ID = dbo.ModeloPC.MODELO_ID INNER JOIN

dbo.ModeloPC_Tipo ON dbo.ModeloPC.MOD_Tipo = dbo.ModeloPC_Tipo.MOD_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.SERVICO.S_NomeDir, dbo.ModeloPC_Tipo.MOD_CustoUnit, dbo.ModeloPC_Tipo.MOD_Nome

HAVING (dbo.SERVICO.S_NomeDir = @.Direccao)

) SubQUery

UNION

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID =1 OR dbo.HARDWARE.MONITOR_ID=2) AND dbo.SERVICO.S_NomeDir=@.Direccao

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

) SubQUery

END

OUTPUT:
Desktops 166 433,09 71892,94
Portáteis 3 675,84 2027,52
TFT 15 166 22,08 3665,28
TFT 17 3 24,35 73,05

How can I SUM the last Column of the 2 queries? How can I SUM (Total)?

Thanks!!!

|||

--1.SUM the last two columns

SELECT t.Item, t.Unidades, (t.CustoUnitario+t.Total) AS LAST2Sum FROM (Your UNION result) t

--2.SUM your TOTAL

SELECT SUM(t.Total) AS SumTotal FROM (Your UNION result) t

Problem in a CROSS to SQL

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:

ID

ID_APLIC

CN1

826

CN1

584

CN1

524

CN1

370

CN1

22

CN2

826

CN2

584

CN2

370

CN2

22

CN3

826

CN3

584

CN3

370

CN3

22

CN4

826

CN4

584

CN4

524

CN4

370

CN4

22

CN5

826

CN5

584

CN5

370

CN5

22

CN8

865

CN8

826

CN8

687

CN8

645

CN8

584

CN8

388

CN8

370

CN8

141

CN8

122

CN8

22

CN8

20

CN14

826

CN14

584

CN14

388

CN14

370

CN14

141

CN14

122

CN14

22

CN14

20

CN15

826

CN15

584

CN15

388

CN15

370

CN15

211

CN15

141

CN15

122

CN15

77

CN15

22

CN15

20

CN22

826

CN22

584

CN22

524

CN22

405

CN22

370

CN22

334

CN22

179

CN22

22

CN29

914

CN29

826

CN29

584

CN29

405

CN29

370

CN29

22

CN29

20

CN36

584

CN36

327

CN36

122

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.

Profile

ID_Profile

Nome

1

Profile 1

2

Profile 2

3

Profile 3

4

Profile 4

Profile_AplicID

AplicID_Profile

ID_Profile

22

1

370

1

524

1

584

1

826

1

Computername

CN_ID

ID_Profile

CN1

1

CN4

1

…

…

COULD YOU HELP ME?!!
THANKS FRIEND!!!

|||

Anyone need more help to understand my problem?
Thanks!!!

|||Anyonne there?