Help in Coalescing distinct values

Hi all,

I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)
SELECT DISTINCT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

The following is one of the method

Code Snippet

CREATE TABLE #Tbl_Request

(

ID INT,

SkillCategoryID INT

)

GO

CREATE TABLE #Lkp_SkillCategory

(

ID INT,

Skill varchar(50)

)

GO

INSERT INTO #Tbl_Request VALUES(1,0)

INSERT INTO #Tbl_Request VALUES(2,1)

INSERT INTO #Tbl_Request VALUES(3,2)

INSERT INTO #Tbl_Request VALUES(4,0)

INSERT INTO #Tbl_Request VALUES(5,2)

INSERT INTO #Tbl_Request VALUES(6,2)

INSERT INTO #Tbl_Request VALUES(7,1)

GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')

INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')

INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')

GO

--Add Temp table to hold distinct values

create table #temp ( Skill varchar(50) )

Insert into #temp(Skill)

SELECT distinct #Lkp_SkillCategory.Skill

FROM #Tbl_Request INNER JOIN

#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID

--Coalesce from Temp table

DECLARE @.listSkills nvarchar(max)

SELECT @.listSkills= COALESCE(@.listSkills+',','')+'['+ #Temp.Skill+']'

FROM #Temp

SELECT @.listSkills

--Drop the tables

DROP TABLE #Temp,#Lkp_SkillCategory,#Tbl_Request

|||

or you can try this one

Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO

DECLARE @.listSkills nvarchar(max)

select @.listSkills = COALESCE(@.listSkills+',','') + '[' + Skill + '] '
FROM
(
SELECT distinct Skill
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
) tbl

SET @.listSkills=(SELECT Skill = @.listSkills)
SELECT @.listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

Thanks,

R@.j

|||

Code Snippet

Declare @.OutputString varchar(max)

select @.OutputString = IsNull(@.OutputString + ', ' + sValue , sValue)

from (

select distinct '[' + Skill + ']' as [sValue]

from [MyTable]

) as [SubQuery]

select @.OutputString

|||Thank you both rusag2 and Raj for the answer. Works perfectly fine.

Help in Case statement

Hi,

I have to use case statement in where condition of a sql query. I get an error

Incorrect syntax near '<'.

Declare @.iPostingID int

set @.iPostingId = 1

Select PostingID from JobsDB_JobPostings where DateDiff(day,PostingDate,getdate())

case @.iPostingId

when 1 then '<1'

when 2 then '<7'

when 3 then '<30'

when 4 then '<60'

when 5 then '<90'

end

Can anyone help me please

Thanks,

Uma Ramiya

Hi,

Maybe you want this:

Code Snippet

SELECT

CASE PostingID

WHEN 1 THEN '<1'

WHEN 2 THEN '<7'

WHEN 3 THEN '<30'

WHEN 4 THEN '<60'

WHEN 5 THEN '<90'

END

FROM JobsDB_JobPostings WHERE DATEDIFF(DAY,PostingDate,GETDATE())

Regards,

Janos

|||The parameter @.iPostingId is used for comparison and not the column field.|||Well, you need the select before the CASE|||Hi,

We use similar construct in our daily life here. Have a look on following query, this will really work.

DECLARE @.iPostingID int

SET @.iPostingId = 1

SELECT
PostingID
FROM
JobsDB_JobPostings
WHERE
DateDiff(day,PostingDate,getdate()) < (CASE
WHEN @.iPostingId = 1 THEN 1
WHEN @.iPostingId = 2 THEN 7
WHEN @.iPostingId = 3 THEN 30
WHEN @.iPostingId = 4 THEN 60
WHEN @.iPostingId = 5 THEN 90
END)|||

Hi I have the following case.i would like a precise and efficient SQL Statement for it.

I have 7 parameters which are used for comparison. I need to run the query with these parameters. which ever is not null should be compared and the null ones should be ignored. AS a result I end with lots of "if' statements as the parameters form a lot of copmbination. Is there any way to cut short the code

ALTER PROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]

@.sSkill varchar(50),

@.iCountryID int=NULL,

@.iStateID int=NULL,

@.iJobTypeID int=NULL,

@.iMinSal int=NULL,

@.iMaxSal int NULL,

@.iPeriodPosted int NULL

AS

IF @.iCountryID!=NULL AND @.iStateID=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted != NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

Help in CASE function

Thanks Rudy for the answer of my previous thread.
Here's another scenario of my problem that i hardly get the correct query.

I have a tables with fields and sample records in my database;

Table: tblPo
FIELDS RECORD1
po_number: PO10001
entered_by: chris
approved_by: albert

Table: tblUser
FIELDS RECORD1 RECORD2
user_id: chris albert
first_name: Christopher Alberto
mi: S W
last_name: Lambert Del Puerto

So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.

Now, I need to get a result like this:

PO # Prepared by Approved by
PO10001 Christopher S. Lambert Alberto W. Del Puerto

My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.

Any help? thanks in advance

BernieHere you go :

use pubs
go
create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
go
create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
go
insert into tblPO values('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
go
select
a.po_number,
isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by
from
tblPO a,
tbluser b,
tbluser c
where
a.entered_by = b.user_id
and a.approved_by = c.user_id

go
drop table tbluser
go
drop table tblpo
go|||create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)|||Originally posted by cvandemaele
create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)

Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null|||SALAMAT! Thank you guyz...

I can proceed now with my report...

Berniesql

Help getting multiple listings

I'm trying this simple query but getting multiple listings for
change_number field

SELECT c.Change_Number, c.Submission_Date, c.Short_Description,
c.CurrentStatus, dlv.Name, s.Description, kw.Subcategory
FROM Changes c
INNER JOIN Deliverables dlv ON c.Deliverable = dlv.DlvID
INNER JOIN Keywords kw ON c.Deliverable_Keyword = kw.KywID
INNER JOIN Status s ON c.CurrentStatus = s.Status
WHERE (c.Application = 3) AND (c.ChangeType = 'CR')
AND (dlv.Name = 'Common Route') AND (c.ProductGroup = 1)
ORDER BY c.Submission_Date DESC

732393/7/2005Publish HasReportableParts100Common
RouteOpen-FiledTEF Integration
732393/7/2005Publish HasReportableParts100Common
RouteOpen-FiledTEF Integration
732393/7/2005Publish HasReportableParts100Common
RouteOpen-FiledTEF Integration
732393/7/2005Publish HasReportableParts100Common
RouteOpen-FiledTEF Integration

Any ideas what might be wrong here ?

thanks
Sunitnot enough information, but anyway: have you tried DISTINCT keyword?|||On 12 Aug 2005 09:46:10 -0700, sjoshi wrote:

>I'm trying this simple query but getting multiple listings for
>change_number field
>SELECT c.Change_Number, c.Submission_Date, c.Short_Description,
>c.CurrentStatus, dlv.Name, s.Description, kw.Subcategory
>FROM Changes c
>INNER JOIN Deliverables dlv ON c.Deliverable = dlv.DlvID
>INNER JOIN Keywords kw ON c.Deliverable_Keyword = kw.KywID
>INNER JOIN Status s ON c.CurrentStatus = s.Status
>WHERE (c.Application = 3) AND (c.ChangeType = 'CR')
>AND (dlv.Name = 'Common Route') AND (c.ProductGroup = 1)
>ORDER BY c.Submission_Date DESC
(snip)
>Any ideas what might be wrong here ?

Hi Sunit,

That means that you have more than one matching row in at least one of
the joined tables. Copy and paste the code below into Query Analyzer to
see how rows can be duplicated in a join.

To solve it, we need to know more about your data, tables and
requirements. See www.aspfaq.com/5006.

CREATE TABLE Test1 (A int NOT NULL PRIMARY KEY,
B int NOT NULL)
CREATE TABLE Test2 (C int NOT NULL PRIMARY KEY,
D int NOT NULL)
INSERT INTO Test1 (A, B)
SELECT 1, 1
UNION ALL
SELECT 2, 1
INSERT INTO Test2 (C, D)
SELECT 1, 1
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 2

-- Show what's in the tables
SELECT A, B FROM Test1
SELECT C, D FROM Test2

-- Do a join - some row match two other rwos
SELECT Test1.A, Test1.B
, Test2.C, Test2.D
FROM Test1
INNER JOIN Test2 ON Test2.D = Test1.A

-- Same join - but this time, hide some of the columns
-- now we really appear to ahve duplicates!
SELECT Test1.A, Test1.B
-- , Test2.C, Test2.D
FROM Test1
INNER JOIN Test2 ON Test2.D = Test1.A

-- Done - clean up the used tables
DROP TABLE Test1
DROP TABLE Test2

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Help for Sql Query

I am writing one procedure and in that i am giving three inputs ie account number and from date and to date, and i will give any one inupt ie account number or (from and to dates).

So in the procedure i want to select records from table and in where condition depending on input ie account number or from and to dates, so i am not able to write query,

I will write the query here what i know, please corrct the query bcoz i am getting error

Select
*
From Mf_Tran_Reg
Where
mft_fundcd='ABN' and mft_purred='P'
if @.Folio = '' --if account number is null
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
enddo the conditional branching earlier,

if @.Folio = '' --if account number is null

select * from tbl where param1 and param2 and param3

else

select * from tbl where param2 and param3

Quote:

Originally Posted by majidsql

I am writing one procedure and in that i am giving three inputs ie account number and from date and to date, and i will give any one inupt ie account number or (from and to dates).

So in the procedure i want to select records from table and in where condition depending on input ie account number or from and to dates, so i am not able to write query,

I will write the query here what i know, please corrct the query bcoz i am getting error

Select
*
From Mf_Tran_Reg
Where
mft_fundcd='ABN' and mft_purred='P'
if @.Folio = '' --if account number is null
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
end

Help for Query

I have two tables; Table1 and Table2 with some sample data:
Table1
CustNum
C041
C042
C043
C044
Table2
A01 A02 A03 A04
100.00 120.00 150.00 160.00
200.00 210.00 250.00 260.00
300.00 310.00 320.00 350.00
150.00 160.00 170.00 180.00
My results should look like this out of above two tables. You will see that
column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName a
s
data against CustName (Table1) and values (Table2).
CustName ColumnName Values
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
Your assistance will be appreciated.
Thanks...FrazIt would be so much easier if your data were Normalized. And you don't have
any form of a PK-FK relationship between the tables.
Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a simp
le JOIN could be used. You might find this article useful: http://www.datamodel.or
g/No...ationRules.html
The following works to provide you the solution you seek -but it is a bit aw
kward and will be difficult to scale.
CREATE TABLE Table1
( CustNum varchar(10) )
GO
INSERT INTO Table1 VALUES ( 'C041' )
INSERT INTO Table1 VALUES ( 'C042' )
INSERT INTO Table1 VALUES ( 'C043' )
INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE Table2
( A01 decimal
, A02 decimal
, A03 decimal
, A04 decimal
)
GO
INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT
CustNum
, 'A01' AS 'ColumnName'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C041'
UNION
SELECT
CustNum
, 'A02'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C042'
UNION
SELECT
CustNum
, 'A03'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C043'
UNION
SELECT
CustNum
, 'A04'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum = 'C044'
ORDER BY
CustNum
, ColumnName
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D06-47B6-A2E3-513135
CE387D@.microsoft.com...
>I have two tables; Table1 and Table2 with some sample data:
>
> Table1
> CustNum
> C041
> C042
> C043
> C044
>
> Table2
> A01 A02 A03 A04
> 100.00 120.00 150.00 160.00
> 200.00 210.00 250.00 260.00
> 300.00 310.00 320.00 350.00
> 150.00 160.00 170.00 180.00
>
> My results should look like this out of above two tables. You will see tha
t
> column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName
as
> data against CustName (Table1) and values (Table2).
>
> CustName ColumnName Values
> C041 A01 100.00
> C041 A02 120.00
> C041 A03 150.00
> C041 A04 160.00
> C042 A01 200.00
> C042 A02 210.00
> C042 A03 250.00
> C042 A04 260.00
> C043 A01 300.00
> C043 A02 310.00
> C043 A03 320.00
> C043 A04 350.00
> C044 A01 150.00
> C044 A02 160.00
> C044 A03 170.00
> C044 A04 180.00
>
> Your assistance will be appreciated.
> Thanks...Fraz|||On Tue, 11 Jul 2006 06:44:48 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>It would be so much easier if your data were Normalized. And you don't have
any form of a PK-FK relationship between the tables.
>Based upon this use, Table2 should really be CustNum, ColumnName,
>ColValue. Then a simple JOIN could be used. You might find this
>article useful: http://www.datamodel.org/NormalizationRules.html
Arnie, I think it is even worse than you described. As I read the
original query, the result set assumes that the two tables are matched
up first-row-to-first, second-row-to-second, etc. Even your query
can't fix THAT problem.
Roy|||I appreciate your feedback, Arnie. We receive the data file from external
source.
The CustNum in Table1 has about 1000 records. Table2 also has 17000 records.
If the sample works well then I wil join other tables which are identical to
Table2 and columnname like B01, B02, C01, C02 etc.
I am wondering if there is an easier way because using a UNION for each
CustNum will make the script too big. Thanks again. Please free to provide
your feedback.
Thanks...Fraz
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It would be so much easier if your data were Normalized. And you don't hav
e any form of a PK-FK relationship between the tables.
> Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a si
mple JOIN could be used. You might find this article useful: http://www.datamodel.
org/No...ationRules.html
> The following works to provide you the solution you seek -but it is a bit
awkward and will be difficult to scale.
> CREATE TABLE Table1
> ( CustNum varchar(10) )
> GO
> INSERT INTO Table1 VALUES ( 'C041' )
> INSERT INTO Table1 VALUES ( 'C042' )
> INSERT INTO Table1 VALUES ( 'C043' )
> INSERT INTO Table1 VALUES ( 'C044' )
> CREATE TABLE Table2
> ( A01 decimal
> , A02 decimal
> , A03 decimal
> , A04 decimal
> )
> GO
> INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
> INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
> INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
> INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
>
> SELECT
> CustNum
> , 'A01' AS 'ColumnName'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C041'
> UNION
> SELECT
> CustNum
> , 'A02'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C042'
> UNION
> SELECT
> CustNum
> , 'A03'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C043'
> UNION
> SELECT
> CustNum
> , 'A04'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C044'
> ORDER BY
> CustNum
> , ColumnName
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D0
6-47B6-A2E3-513135CE387D@.microsoft.com...|||I have made some comments below.
On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:

>I have two tables; Table1 and Table2 with some sample data:
>Table1
>CustNum
>C041
>C042
>C043
>C044
>Table2
>A01 A02 A03 A04
>100.00 120.00 150.00 160.00
>200.00 210.00 250.00 260.00
>300.00 310.00 320.00 350.00
>150.00 160.00 170.00 180.00
These are ALL the rows? No key? Even denormalized tables need a key.

>My results should look like this out of above two tables. You will see that
>column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName
as
>data against CustName (Table1) and values (Table2).
>CustName ColumnName Values
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
It appears that, somehow, a specific CustNum from Table1 (the "first")
is supposed to match up with the "first" row in Table2'

>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
Same again, but the "second"?

>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
etc.

>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
etc.

>Your assistance will be appreciated.
>Thanks...Fraz
I see no way to produce the results you requested from the tables
given. Without CustNum on Table2 there is no way to match the two
tables up the way your sample output specifies. Relational tables
have no order, there is no "first", "next" or "last", so there can be
no joining on such a basis.
Please take the time to learn about data normalization. However long
it takes to learn - and it is really quite simple - compared to the
time you will waste trying to work with things like this it will pay
off easily.
Roy Harvey
Beacon Falls, CT|||My apologies. There are PK and FK keys. Between Table1 and Table2 there is
one-to-one relationship. Each table has 50-60 columns but I will precisely
re-append the tables:
Table1
Id (PK) CustNum
100 C041
101 C042
102 C043
105 C044
Table2
ID (FK) A01 A02 A03 A04
100 100.00 120.00 150.00 160.00
101 200.00 210.00 250.00 260.00
102 300.00 310.00 320.00 350.00
105 150.00 160.00 170.00 180.00
Results required by user in the output file as follows:
CustName ColumnName Value
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
You will notice that values in ColumnName is actually column names in Table2
.
Thanks...Fraz
"Roy Harvey" wrote:

> I have made some comments below.
> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
>
> These are ALL the rows? No key? Even denormalized tables need a key.
>
> It appears that, somehow, a specific CustNum from Table1 (the "first")
> is supposed to match up with the "first" row in Table2'
>
> Same again, but the "second"?
>
> etc.
>
> etc.
>
> I see no way to produce the results you requested from the tables
> given. Without CustNum on Table2 there is no way to match the two
> tables up the way your sample output specifies. Relational tables
> have no order, there is no "first", "next" or "last", so there can be
> no joining on such a basis.
> Please take the time to learn about data normalization. However long
> it takes to learn - and it is really quite simple - compared to the
> time you will waste trying to work with things like this it will pay
> off easily.
> Roy Harvey
> Beacon Falls, CT
>|||Untested, but this should do it.
SELECT CustNum,
ColumName =
CASE N.I
WHEN 1 THEN 'A01'
WHEN 2 THEN 'A02'
WHEN 3 THEN 'A03'
WHEN 4 THEN 'A04'
END,
Value =
CASE N.I
WHEN 1 THEN A01
WHEN 2 THEN A02
WHEN 3 THEN A03
WHEN 4 THEN A04
END
FROM Table1 as A
JOIN Table2 as B
ON A.Id = B.Id
CROSS JOIN
(select 1 as I UNION ALL
select 2 as I UNION ALL
select 3 as I UNION ALL
select 4 as I) as N
Roy Harvey
Beacon Falls, CT
On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>My apologies. There are PK and FK keys. Between Table1 and Table2 there is
>one-to-one relationship. Each table has 50-60 columns but I will precisely
>re-append the tables:
>Table1
>Id (PK) CustNum
>100 C041
>101 C042
>102 C043
>105 C044
>Table2
>ID (FK) A01 A02 A03 A04
>100 100.00 120.00 150.00 160.00
>101 200.00 210.00 250.00 260.00
>102 300.00 310.00 320.00 350.00
>105 150.00 160.00 170.00 180.00
>Results required by user in the output file as follows:
>CustName ColumnName Value
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
>You will notice that values in ColumnName is actually column names in Table
2.
>Thanks...Fraz
>
>"Roy Harvey" wrote:
>|||It looks promising. I will use this concept to add other tables. Thanks a lo
t
Roy and Arnie. Regards, Fraz.
"Roy Harvey" wrote:

> Untested, but this should do it.
> SELECT CustNum,
> ColumName =
> CASE N.I
> WHEN 1 THEN 'A01'
> WHEN 2 THEN 'A02'
> WHEN 3 THEN 'A03'
> WHEN 4 THEN 'A04'
> END,
> Value =
> CASE N.I
> WHEN 1 THEN A01
> WHEN 2 THEN A02
> WHEN 3 THEN A03
> WHEN 4 THEN A04
> END
> FROM Table1 as A
> JOIN Table2 as B
> ON A.Id = B.Id
> CROSS JOIN
> (select 1 as I UNION ALL
> select 2 as I UNION ALL
> select 3 as I UNION ALL
> select 4 as I) as N
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
>
>|||Hi,
Try this querry
SELECT a.custname,b.col,b.val FROM table a,
(
SELECT 'A01' as 'col', a01 as 'val' from table2
UNION ALL
SELECT 'A02' as 'col' , a02 as 'val' from table2
UNION ALL
SELECT 'A03' as 'col' , a03 as 'val' from table2
UNION ALL
SELECT 'A04' as 'col' , a04 as 'val' from table2
) b
ORDER BY a.custname
Amol Lembhe
"Fraz" wrote:
[vbcol=seagreen]
> It looks promising. I will use this concept to add other tables. Thanks a
lot
> Roy and Arnie. Regards, Fraz.
> "Roy Harvey" wrote:
>|||Thanks Amol. Fraz
"Amol Lembhe" wrote:
[vbcol=seagreen]
> Hi,
> Try this querry
> SELECT a.custname,b.col,b.val FROM table a,
> (
> SELECT 'A01' as 'col', a01 as 'val' from table2
> UNION ALL
> SELECT 'A02' as 'col' , a02 as 'val' from table2
> UNION ALL
> SELECT 'A03' as 'col' , a03 as 'val' from table2
> UNION ALL
> SELECT 'A04' as 'col' , a04 as 'val' from table2
> ) b
> ORDER BY a.custname
> Amol Lembhe
>
> "Fraz" wrote:
>

Help for Query

I have two tables; Table1 and Table2 with some sample data:
Table1
CustNum
C041
C042
C043
C044
Table2
A01 A02 A03 A04
100.00 120.00 150.00 160.00
200.00 210.00 250.00 260.00
300.00 310.00 320.00 350.00
150.00 160.00 170.00 180.00
My results should look like this out of above two tables. You will see that
column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
data against CustName (Table1) and values (Table2).
CustName ColumnName Values
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
Your assistance will be appreciated.
Thanks...FrazThis is a multi-part message in MIME format.
--=_NextPart_000_05FB_01C6A4B5.80E9A730
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It would be so much easier if your data were Normalized. And you don't =have any form of a PK-FK relationship between the tables.
Based upon this use, Table2 should really be CustNum, ColumnName, =ColValue. Then a simple JOIN could be used. You might find this article =useful: http://www.datamodel.org/NormalizationRules.html
The following works to provide you the solution you seek -but it is a =bit awkward and will be difficult to scale.
CREATE TABLE Table1
( CustNum varchar(10) )
GO
INSERT INTO Table1 VALUES ( 'C041' )
INSERT INTO Table1 VALUES ( 'C042' )
INSERT INTO Table1 VALUES ( 'C043' )
INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE Table2
( A01 decimal
, A02 decimal
, A03 decimal
, A04 decimal
)
GO
INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT CustNum
, 'A01' AS 'ColumnName'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C041'
UNION
SELECT CustNum
, 'A02'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C042'
UNION
SELECT CustNum
, 'A03'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C043'
UNION
SELECT CustNum
, 'A04'
, A01
FROM Table1
CROSS JOIN Table2
WHERE CustNum =3D 'C044'
ORDER BY CustNum
, ColumnName
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message =news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...
>I have two tables; Table1 and Table2 with some sample data: > > Table1
> CustNum
> C041 > C042
> C043
> C044
> > Table2
> A01 A02 A03 A04
> 100.00 120.00 150.00 160.00 > 200.00 210.00 250.00 260.00
> 300.00 310.00 320.00 350.00
> 150.00 160.00 170.00 180.00
> > My results should look like this out of above two tables. You will see =that > column name A01, A02, A03 and A04 from Table2 has been shown in =ColumnName as > data against CustName (Table1) and values (Table2). > > CustName ColumnName Values
> C041 A01 100.00
> C041 A02 120.00
> C041 A03 150.00
> C041 A04 160.00
> C042 A01 200.00
> C042 A02 210.00
> C042 A03 250.00
> C042 A04 260.00
> C043 A01 300.00
> C043 A02 310.00
> C043 A03 320.00
> C043 A04 350.00
> C044 A01 150.00
> C044 A02 160.00
> C044 A03 170.00
> C044 A04 180.00
> > Your assistance will be appreciated.
> Thanks...Fraz
--=_NextPart_000_05FB_01C6A4B5.80E9A730
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It would be so much easier if your data =were Normalized. And you don't have any form of a PK-FK relationship between =the tables.
Based upon this use, Table2 should =really be CustNum, ColumnName, ColValue. Then a simple JOIN could be used. You =might find this article useful:
The following works to provide you =the solution you seek -but it is a bit awkward and will be difficult to scale.
CREATE TABLE =Table1 ( CustNum varchar(10) )GO
INSERT INTO Table1 VALUES ( ='C041' )INSERT INTO Table1 VALUES ( 'C042' )INSERT INTO Table1 VALUES ( ='C043' )INSERT INTO Table1 VALUES ( 'C044' )
CREATE TABLE =Table2 ( A01 decimal =, A02 decimal , A03 decimal , A04 =decimal )GO
INSERT INTO Table2 VALUES ( =100.00, 120.00, 150.00, 160.00 )INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, =260.00 )INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 =)INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
SELECT CustNum , 'A01' AS ='ColumnName' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum ==3D 'C041'
UNION
SELECT CustNum , 'A02' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C042'
UNION
SELECT CustNum , 'A03' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C043'
UNION
SELECT CustNum , 'A04' , A01FROM Table1 CROSS JOIN Table2WHERE CustNum =3D ='C044'
ORDER BY CustNum , ColumnName
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Fraz" wrote in message news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...>I =have two tables; Table1 and Table2 with some sample data: > > =Table1> CustNum> C041 > C042> C043> C044> => Table2> A01 A02 A03 A04> 100.00 120.00 150.00 =160.00 > 200.00 210.00 250.00 260.00> 300.00 310.00 =320.00 350.00> 150.00 160.00 =170.00 180.00> > My results should look like this out of above =two tables. You will see that > column name A01, A02, A03 and A04 =from Table2 has been shown in ColumnName as > data against CustName (Table1) =and values (Table2). > > CustName ColumnName =Values> =C041 A01 &nbs=p; 100.00> =C041 A02 &nbs=p; 120.00> =C041 A03 &nbs=p; 150.00> =C041 A04 &nbs=p; 160.00> =C042 A01 &nbs=p; 200.00> =C042 A02 &nbs=p; 210.00> =C042 A03 &nbs=p; 250.00> =C042 A04 &nbs=p; 260.00> =C043 A01 &nbs=p; 300.00> =C043 A02 &nbs=p; 310.00> =C043 A03 &nbs=p; 320.00> =C043 A04 &nbs=p; 350.00> =C044 A01 &nbs=p; 150.00> =C044 A02 &nbs=p; 160.00> =C044 A03 &nbs=p; 170.00> =C044 A04 &nbs=p; 180.00> > Your assistance will be appreciated.> Thanks...Fraz

---

--=_NextPart_000_05FB_01C6A4B5.80E9A730--|||On Tue, 11 Jul 2006 06:44:48 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>It would be so much easier if your data were Normalized. And you don't have any form of a PK-FK relationship between the tables.
>Based upon this use, Table2 should really be CustNum, ColumnName,
>ColValue. Then a simple JOIN could be used. You might find this
>article useful: http://www.datamodel.org/NormalizationRules.html
Arnie, I think it is even worse than you described. As I read the
original query, the result set assumes that the two tables are matched
up first-row-to-first, second-row-to-second, etc. Even your query
can't fix THAT problem.
Roy|||I appreciate your feedback, Arnie. We receive the data file from external
source.
The CustNum in Table1 has about 1000 records. Table2 also has 17000 records.
If the sample works well then I wil join other tables which are identical to
Table2 and columnname like B01, B02, C01, C02 etc.
I am wondering if there is an easier way because using a UNION for each
CustNum will make the script too big. Thanks again. Please free to provide
your feedback.
Thanks...Fraz
"Arnie Rowland" wrote:
> It would be so much easier if your data were Normalized. And you don't have any form of a PK-FK relationship between the tables.
> Based upon this use, Table2 should really be CustNum, ColumnName, ColValue. Then a simple JOIN could be used. You might find this article useful: http://www.datamodel.org/NormalizationRules.html
> The following works to provide you the solution you seek -but it is a bit awkward and will be difficult to scale.
> CREATE TABLE Table1
> ( CustNum varchar(10) )
> GO
> INSERT INTO Table1 VALUES ( 'C041' )
> INSERT INTO Table1 VALUES ( 'C042' )
> INSERT INTO Table1 VALUES ( 'C043' )
> INSERT INTO Table1 VALUES ( 'C044' )
> CREATE TABLE Table2
> ( A01 decimal
> , A02 decimal
> , A03 decimal
> , A04 decimal
> )
> GO
> INSERT INTO Table2 VALUES ( 100.00, 120.00, 150.00, 160.00 )
> INSERT INTO Table2 VALUES ( 200.00, 210.00, 250.00, 260.00 )
> INSERT INTO Table2 VALUES ( 300.00, 310.00, 320.00, 350.00 )
> INSERT INTO Table2 VALUES ( 150.00, 160.00, 170.00, 180.00 )
>
> SELECT
> CustNum
> , 'A01' AS 'ColumnName'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C041'
> UNION
> SELECT
> CustNum
> , 'A02'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C042'
> UNION
> SELECT
> CustNum
> , 'A03'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C043'
> UNION
> SELECT
> CustNum
> , 'A04'
> , A01
> FROM Table1
> CROSS JOIN Table2
> WHERE CustNum = 'C044'
> ORDER BY
> CustNum
> , ColumnName
>
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message news:9BC05939-8D06-47B6-A2E3-513135CE387D@.microsoft.com...
> >I have two tables; Table1 and Table2 with some sample data:
> >
> > Table1
> > CustNum
> > C041
> > C042
> > C043
> > C044
> >
> > Table2
> > A01 A02 A03 A04
> > 100.00 120.00 150.00 160.00
> > 200.00 210.00 250.00 260.00
> > 300.00 310.00 320.00 350.00
> > 150.00 160.00 170.00 180.00
> >
> > My results should look like this out of above two tables. You will see that
> > column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > data against CustName (Table1) and values (Table2).
> >
> > CustName ColumnName Values
> > C041 A01 100.00
> > C041 A02 120.00
> > C041 A03 150.00
> > C041 A04 160.00
> > C042 A01 200.00
> > C042 A02 210.00
> > C042 A03 250.00
> > C042 A04 260.00
> > C043 A01 300.00
> > C043 A02 310.00
> > C043 A03 320.00
> > C043 A04 350.00
> > C044 A01 150.00
> > C044 A02 160.00
> > C044 A03 170.00
> > C044 A04 180.00
> >
> > Your assistance will be appreciated.
> > Thanks..|||I have made some comments below.
On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>I have two tables; Table1 and Table2 with some sample data:
>Table1
>CustNum
>C041
>C042
>C043
>C044
>Table2
>A01 A02 A03 A04
>100.00 120.00 150.00 160.00
>200.00 210.00 250.00 260.00
>300.00 310.00 320.00 350.00
>150.00 160.00 170.00 180.00
These are ALL the rows? No key? Even denormalized tables need a key.
>My results should look like this out of above two tables. You will see that
>column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
>data against CustName (Table1) and values (Table2).
>CustName ColumnName Values
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
It appears that, somehow, a specific CustNum from Table1 (the "first")
is supposed to match up with the "first" row in Table2'
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
Same again, but the "second"?
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
etc.
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
etc.
>Your assistance will be appreciated.
>Thanks...Fraz
I see no way to produce the results you requested from the tables
given. Without CustNum on Table2 there is no way to match the two
tables up the way your sample output specifies. Relational tables
have no order, there is no "first", "next" or "last", so there can be
no joining on such a basis.
Please take the time to learn about data normalization. However long
it takes to learn - and it is really quite simple - compared to the
time you will waste trying to work with things like this it will pay
off easily.
Roy Harvey
Beacon Falls, CT|||My apologies. There are PK and FK keys. Between Table1 and Table2 there is
one-to-one relationship. Each table has 50-60 columns but I will precisely
re-append the tables:
Table1
Id (PK) CustNum
100 C041
101 C042
102 C043
105 C044
Table2
ID (FK) A01 A02 A03 A04
100 100.00 120.00 150.00 160.00
101 200.00 210.00 250.00 260.00
102 300.00 310.00 320.00 350.00
105 150.00 160.00 170.00 180.00
Results required by user in the output file as follows:
CustName ColumnName Value
C041 A01 100.00
C041 A02 120.00
C041 A03 150.00
C041 A04 160.00
C042 A01 200.00
C042 A02 210.00
C042 A03 250.00
C042 A04 260.00
C043 A01 300.00
C043 A02 310.00
C043 A03 320.00
C043 A04 350.00
C044 A01 150.00
C044 A02 160.00
C044 A03 170.00
C044 A04 180.00
You will notice that values in ColumnName is actually column names in Table2.
Thanks...Fraz
"Roy Harvey" wrote:
> I have made some comments below.
> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
> >I have two tables; Table1 and Table2 with some sample data:
> >
> >Table1
> >CustNum
> >C041
> >C042
> >C043
> >C044
> >
> >Table2
> >A01 A02 A03 A04
> >100.00 120.00 150.00 160.00
> >200.00 210.00 250.00 260.00
> >300.00 310.00 320.00 350.00
> >150.00 160.00 170.00 180.00
> These are ALL the rows? No key? Even denormalized tables need a key.
> >My results should look like this out of above two tables. You will see that
> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> >data against CustName (Table1) and values (Table2).
> >
> >CustName ColumnName Values
> >C041 A01 100.00
> >C041 A02 120.00
> >C041 A03 150.00
> >C041 A04 160.00
> It appears that, somehow, a specific CustNum from Table1 (the "first")
> is supposed to match up with the "first" row in Table2'
> >C042 A01 200.00
> >C042 A02 210.00
> >C042 A03 250.00
> >C042 A04 260.00
> Same again, but the "second"?
> >C043 A01 300.00
> >C043 A02 310.00
> >C043 A03 320.00
> >C043 A04 350.00
> etc.
> >C044 A01 150.00
> >C044 A02 160.00
> >C044 A03 170.00
> >C044 A04 180.00
> etc.
> >
> >Your assistance will be appreciated.
> >Thanks...Fraz
> I see no way to produce the results you requested from the tables
> given. Without CustNum on Table2 there is no way to match the two
> tables up the way your sample output specifies. Relational tables
> have no order, there is no "first", "next" or "last", so there can be
> no joining on such a basis.
> Please take the time to learn about data normalization. However long
> it takes to learn - and it is really quite simple - compared to the
> time you will waste trying to work with things like this it will pay
> off easily.
> Roy Harvey
> Beacon Falls, CT
>|||Untested, but this should do it.
SELECT CustNum,
ColumName = CASE N.I
WHEN 1 THEN 'A01'
WHEN 2 THEN 'A02'
WHEN 3 THEN 'A03'
WHEN 4 THEN 'A04'
END,
Value = CASE N.I
WHEN 1 THEN A01
WHEN 2 THEN A02
WHEN 3 THEN A03
WHEN 4 THEN A04
END
FROM Table1 as A
JOIN Table2 as B
ON A.Id = B.Id
CROSS JOIN
(select 1 as I UNION ALL
select 2 as I UNION ALL
select 3 as I UNION ALL
select 4 as I) as N
Roy Harvey
Beacon Falls, CT
On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
<Fraz@.discussions.microsoft.com> wrote:
>My apologies. There are PK and FK keys. Between Table1 and Table2 there is
>one-to-one relationship. Each table has 50-60 columns but I will precisely
>re-append the tables:
>Table1
>Id (PK) CustNum
>100 C041
>101 C042
>102 C043
>105 C044
>Table2
>ID (FK) A01 A02 A03 A04
>100 100.00 120.00 150.00 160.00
>101 200.00 210.00 250.00 260.00
>102 300.00 310.00 320.00 350.00
>105 150.00 160.00 170.00 180.00
>Results required by user in the output file as follows:
>CustName ColumnName Value
>C041 A01 100.00
>C041 A02 120.00
>C041 A03 150.00
>C041 A04 160.00
>C042 A01 200.00
>C042 A02 210.00
>C042 A03 250.00
>C042 A04 260.00
>C043 A01 300.00
>C043 A02 310.00
>C043 A03 320.00
>C043 A04 350.00
>C044 A01 150.00
>C044 A02 160.00
>C044 A03 170.00
>C044 A04 180.00
>You will notice that values in ColumnName is actually column names in Table2.
>Thanks...Fraz
>
>"Roy Harvey" wrote:
>> I have made some comments below.
>> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
>> <Fraz@.discussions.microsoft.com> wrote:
>> >I have two tables; Table1 and Table2 with some sample data:
>> >
>> >Table1
>> >CustNum
>> >C041
>> >C042
>> >C043
>> >C044
>> >
>> >Table2
>> >A01 A02 A03 A04
>> >100.00 120.00 150.00 160.00
>> >200.00 210.00 250.00 260.00
>> >300.00 310.00 320.00 350.00
>> >150.00 160.00 170.00 180.00
>> These are ALL the rows? No key? Even denormalized tables need a key.
>> >My results should look like this out of above two tables. You will see that
>> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
>> >data against CustName (Table1) and values (Table2).
>> >
>> >CustName ColumnName Values
>> >C041 A01 100.00
>> >C041 A02 120.00
>> >C041 A03 150.00
>> >C041 A04 160.00
>> It appears that, somehow, a specific CustNum from Table1 (the "first")
>> is supposed to match up with the "first" row in Table2'
>> >C042 A01 200.00
>> >C042 A02 210.00
>> >C042 A03 250.00
>> >C042 A04 260.00
>> Same again, but the "second"?
>> >C043 A01 300.00
>> >C043 A02 310.00
>> >C043 A03 320.00
>> >C043 A04 350.00
>> etc.
>> >C044 A01 150.00
>> >C044 A02 160.00
>> >C044 A03 170.00
>> >C044 A04 180.00
>> etc.
>> >
>> >Your assistance will be appreciated.
>> >Thanks...Fraz
>> I see no way to produce the results you requested from the tables
>> given. Without CustNum on Table2 there is no way to match the two
>> tables up the way your sample output specifies. Relational tables
>> have no order, there is no "first", "next" or "last", so there can be
>> no joining on such a basis.
>> Please take the time to learn about data normalization. However long
>> it takes to learn - and it is really quite simple - compared to the
>> time you will waste trying to work with things like this it will pay
>> off easily.
>> Roy Harvey
>> Beacon Falls, CT|||It looks promising. I will use this concept to add other tables. Thanks a lot
Roy and Arnie. Regards, Fraz.
"Roy Harvey" wrote:
> Untested, but this should do it.
> SELECT CustNum,
> ColumName => CASE N.I
> WHEN 1 THEN 'A01'
> WHEN 2 THEN 'A02'
> WHEN 3 THEN 'A03'
> WHEN 4 THEN 'A04'
> END,
> Value => CASE N.I
> WHEN 1 THEN A01
> WHEN 2 THEN A02
> WHEN 3 THEN A03
> WHEN 4 THEN A04
> END
> FROM Table1 as A
> JOIN Table2 as B
> ON A.Id = B.Id
> CROSS JOIN
> (select 1 as I UNION ALL
> select 2 as I UNION ALL
> select 3 as I UNION ALL
> select 4 as I) as N
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> <Fraz@.discussions.microsoft.com> wrote:
> >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> >one-to-one relationship. Each table has 50-60 columns but I will precisely
> >re-append the tables:
> >Table1
> >Id (PK) CustNum
> >100 C041
> >101 C042
> >102 C043
> >105 C044
> >
> >Table2
> >ID (FK) A01 A02 A03 A04
> >100 100.00 120.00 150.00 160.00
> >101 200.00 210.00 250.00 260.00
> >102 300.00 310.00 320.00 350.00
> >105 150.00 160.00 170.00 180.00
> >
> >Results required by user in the output file as follows:
> >CustName ColumnName Value
> >C041 A01 100.00
> >C041 A02 120.00
> >C041 A03 150.00
> >C041 A04 160.00
> >C042 A01 200.00
> >C042 A02 210.00
> >C042 A03 250.00
> >C042 A04 260.00
> >C043 A01 300.00
> >C043 A02 310.00
> >C043 A03 320.00
> >C043 A04 350.00
> >C044 A01 150.00
> >C044 A02 160.00
> >C044 A03 170.00
> >C044 A04 180.00
> >
> >You will notice that values in ColumnName is actually column names in Table2.
> >Thanks...Fraz
> >
> >
> >"Roy Harvey" wrote:
> >
> >> I have made some comments below.
> >>
> >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> >> <Fraz@.discussions.microsoft.com> wrote:
> >>
> >> >I have two tables; Table1 and Table2 with some sample data:
> >> >
> >> >Table1
> >> >CustNum
> >> >C041
> >> >C042
> >> >C043
> >> >C044
> >> >
> >> >Table2
> >> >A01 A02 A03 A04
> >> >100.00 120.00 150.00 160.00
> >> >200.00 210.00 250.00 260.00
> >> >300.00 310.00 320.00 350.00
> >> >150.00 160.00 170.00 180.00
> >>
> >> These are ALL the rows? No key? Even denormalized tables need a key.
> >>
> >> >My results should look like this out of above two tables. You will see that
> >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> >> >data against CustName (Table1) and values (Table2).
> >> >
> >> >CustName ColumnName Values
> >> >C041 A01 100.00
> >> >C041 A02 120.00
> >> >C041 A03 150.00
> >> >C041 A04 160.00
> >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> >> is supposed to match up with the "first" row in Table2'
> >>
> >> >C042 A01 200.00
> >> >C042 A02 210.00
> >> >C042 A03 250.00
> >> >C042 A04 260.00
> >> Same again, but the "second"?
> >>
> >> >C043 A01 300.00
> >> >C043 A02 310.00
> >> >C043 A03 320.00
> >> >C043 A04 350.00
> >> etc.
> >>
> >> >C044 A01 150.00
> >> >C044 A02 160.00
> >> >C044 A03 170.00
> >> >C044 A04 180.00
> >> etc.
> >>
> >> >
> >> >Your assistance will be appreciated.
> >> >Thanks...Fraz
> >>
> >> I see no way to produce the results you requested from the tables
> >> given. Without CustNum on Table2 there is no way to match the two
> >> tables up the way your sample output specifies. Relational tables
> >> have no order, there is no "first", "next" or "last", so there can be
> >> no joining on such a basis.
> >>
> >> Please take the time to learn about data normalization. However long
> >> it takes to learn - and it is really quite simple - compared to the
> >> time you will waste trying to work with things like this it will pay
> >> off easily.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>|||Hi,
Try this querry
SELECT a.custname,b.col,b.val FROM table a,
(
SELECT 'A01' as 'col', a01 as 'val' from table2
UNION ALL
SELECT 'A02' as 'col' , a02 as 'val' from table2
UNION ALL
SELECT 'A03' as 'col' , a03 as 'val' from table2
UNION ALL
SELECT 'A04' as 'col' , a04 as 'val' from table2
) b
ORDER BY a.custname
Amol Lembhe
"Fraz" wrote:
> It looks promising. I will use this concept to add other tables. Thanks a lot
> Roy and Arnie. Regards, Fraz.
> "Roy Harvey" wrote:
> > Untested, but this should do it.
> >
> > SELECT CustNum,
> > ColumName => > CASE N.I
> > WHEN 1 THEN 'A01'
> > WHEN 2 THEN 'A02'
> > WHEN 3 THEN 'A03'
> > WHEN 4 THEN 'A04'
> > END,
> > Value => > CASE N.I
> > WHEN 1 THEN A01
> > WHEN 2 THEN A02
> > WHEN 3 THEN A03
> > WHEN 4 THEN A04
> > END
> > FROM Table1 as A
> > JOIN Table2 as B
> > ON A.Id = B.Id
> > CROSS JOIN
> > (select 1 as I UNION ALL
> > select 2 as I UNION ALL
> > select 3 as I UNION ALL
> > select 4 as I) as N
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > <Fraz@.discussions.microsoft.com> wrote:
> >
> > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > >re-append the tables:
> > >Table1
> > >Id (PK) CustNum
> > >100 C041
> > >101 C042
> > >102 C043
> > >105 C044
> > >
> > >Table2
> > >ID (FK) A01 A02 A03 A04
> > >100 100.00 120.00 150.00 160.00
> > >101 200.00 210.00 250.00 260.00
> > >102 300.00 310.00 320.00 350.00
> > >105 150.00 160.00 170.00 180.00
> > >
> > >Results required by user in the output file as follows:
> > >CustName ColumnName Value
> > >C041 A01 100.00
> > >C041 A02 120.00
> > >C041 A03 150.00
> > >C041 A04 160.00
> > >C042 A01 200.00
> > >C042 A02 210.00
> > >C042 A03 250.00
> > >C042 A04 260.00
> > >C043 A01 300.00
> > >C043 A02 310.00
> > >C043 A03 320.00
> > >C043 A04 350.00
> > >C044 A01 150.00
> > >C044 A02 160.00
> > >C044 A03 170.00
> > >C044 A04 180.00
> > >
> > >You will notice that values in ColumnName is actually column names in Table2.
> > >Thanks...Fraz
> > >
> > >
> > >"Roy Harvey" wrote:
> > >
> > >> I have made some comments below.
> > >>
> > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > >> <Fraz@.discussions.microsoft.com> wrote:
> > >>
> > >> >I have two tables; Table1 and Table2 with some sample data:
> > >> >
> > >> >Table1
> > >> >CustNum
> > >> >C041
> > >> >C042
> > >> >C043
> > >> >C044
> > >> >
> > >> >Table2
> > >> >A01 A02 A03 A04
> > >> >100.00 120.00 150.00 160.00
> > >> >200.00 210.00 250.00 260.00
> > >> >300.00 310.00 320.00 350.00
> > >> >150.00 160.00 170.00 180.00
> > >>
> > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > >>
> > >> >My results should look like this out of above two tables. You will see that
> > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > >> >data against CustName (Table1) and values (Table2).
> > >> >
> > >> >CustName ColumnName Values
> > >> >C041 A01 100.00
> > >> >C041 A02 120.00
> > >> >C041 A03 150.00
> > >> >C041 A04 160.00
> > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > >> is supposed to match up with the "first" row in Table2'
> > >>
> > >> >C042 A01 200.00
> > >> >C042 A02 210.00
> > >> >C042 A03 250.00
> > >> >C042 A04 260.00
> > >> Same again, but the "second"?
> > >>
> > >> >C043 A01 300.00
> > >> >C043 A02 310.00
> > >> >C043 A03 320.00
> > >> >C043 A04 350.00
> > >> etc.
> > >>
> > >> >C044 A01 150.00
> > >> >C044 A02 160.00
> > >> >C044 A03 170.00
> > >> >C044 A04 180.00
> > >> etc.
> > >>
> > >> >
> > >> >Your assistance will be appreciated.
> > >> >Thanks...Fraz
> > >>
> > >> I see no way to produce the results you requested from the tables
> > >> given. Without CustNum on Table2 there is no way to match the two
> > >> tables up the way your sample output specifies. Relational tables
> > >> have no order, there is no "first", "next" or "last", so there can be
> > >> no joining on such a basis.
> > >>
> > >> Please take the time to learn about data normalization. However long
> > >> it takes to learn - and it is really quite simple - compared to the
> > >> time you will waste trying to work with things like this it will pay
> > >> off easily.
> > >>
> > >> Roy Harvey
> > >> Beacon Falls, CT
> > >>
> >|||Thanks Amol. Fraz
"Amol Lembhe" wrote:
> Hi,
> Try this querry
> SELECT a.custname,b.col,b.val FROM table a,
> (
> SELECT 'A01' as 'col', a01 as 'val' from table2
> UNION ALL
> SELECT 'A02' as 'col' , a02 as 'val' from table2
> UNION ALL
> SELECT 'A03' as 'col' , a03 as 'val' from table2
> UNION ALL
> SELECT 'A04' as 'col' , a04 as 'val' from table2
> ) b
> ORDER BY a.custname
> Amol Lembhe
>
> "Fraz" wrote:
> > It looks promising. I will use this concept to add other tables. Thanks a lot
> > Roy and Arnie. Regards, Fraz.
> >
> > "Roy Harvey" wrote:
> >
> > > Untested, but this should do it.
> > >
> > > SELECT CustNum,
> > > ColumName => > > CASE N.I
> > > WHEN 1 THEN 'A01'
> > > WHEN 2 THEN 'A02'
> > > WHEN 3 THEN 'A03'
> > > WHEN 4 THEN 'A04'
> > > END,
> > > Value => > > CASE N.I
> > > WHEN 1 THEN A01
> > > WHEN 2 THEN A02
> > > WHEN 3 THEN A03
> > > WHEN 4 THEN A04
> > > END
> > > FROM Table1 as A
> > > JOIN Table2 as B
> > > ON A.Id = B.Id
> > > CROSS JOIN
> > > (select 1 as I UNION ALL
> > > select 2 as I UNION ALL
> > > select 3 as I UNION ALL
> > > select 4 as I) as N
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > >
> > > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > > <Fraz@.discussions.microsoft.com> wrote:
> > >
> > > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > > >re-append the tables:
> > > >Table1
> > > >Id (PK) CustNum
> > > >100 C041
> > > >101 C042
> > > >102 C043
> > > >105 C044
> > > >
> > > >Table2
> > > >ID (FK) A01 A02 A03 A04
> > > >100 100.00 120.00 150.00 160.00
> > > >101 200.00 210.00 250.00 260.00
> > > >102 300.00 310.00 320.00 350.00
> > > >105 150.00 160.00 170.00 180.00
> > > >
> > > >Results required by user in the output file as follows:
> > > >CustName ColumnName Value
> > > >C041 A01 100.00
> > > >C041 A02 120.00
> > > >C041 A03 150.00
> > > >C041 A04 160.00
> > > >C042 A01 200.00
> > > >C042 A02 210.00
> > > >C042 A03 250.00
> > > >C042 A04 260.00
> > > >C043 A01 300.00
> > > >C043 A02 310.00
> > > >C043 A03 320.00
> > > >C043 A04 350.00
> > > >C044 A01 150.00
> > > >C044 A02 160.00
> > > >C044 A03 170.00
> > > >C044 A04 180.00
> > > >
> > > >You will notice that values in ColumnName is actually column names in Table2.
> > > >Thanks...Fraz
> > > >
> > > >
> > > >"Roy Harvey" wrote:
> > > >
> > > >> I have made some comments below.
> > > >>
> > > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > > >> <Fraz@.discussions.microsoft.com> wrote:
> > > >>
> > > >> >I have two tables; Table1 and Table2 with some sample data:
> > > >> >
> > > >> >Table1
> > > >> >CustNum
> > > >> >C041
> > > >> >C042
> > > >> >C043
> > > >> >C044
> > > >> >
> > > >> >Table2
> > > >> >A01 A02 A03 A04
> > > >> >100.00 120.00 150.00 160.00
> > > >> >200.00 210.00 250.00 260.00
> > > >> >300.00 310.00 320.00 350.00
> > > >> >150.00 160.00 170.00 180.00
> > > >>
> > > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > > >>
> > > >> >My results should look like this out of above two tables. You will see that
> > > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > > >> >data against CustName (Table1) and values (Table2).
> > > >> >
> > > >> >CustName ColumnName Values
> > > >> >C041 A01 100.00
> > > >> >C041 A02 120.00
> > > >> >C041 A03 150.00
> > > >> >C041 A04 160.00
> > > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > > >> is supposed to match up with the "first" row in Table2'
> > > >>
> > > >> >C042 A01 200.00
> > > >> >C042 A02 210.00
> > > >> >C042 A03 250.00
> > > >> >C042 A04 260.00
> > > >> Same again, but the "second"?
> > > >>
> > > >> >C043 A01 300.00
> > > >> >C043 A02 310.00
> > > >> >C043 A03 320.00
> > > >> >C043 A04 350.00
> > > >> etc.
> > > >>
> > > >> >C044 A01 150.00
> > > >> >C044 A02 160.00
> > > >> >C044 A03 170.00
> > > >> >C044 A04 180.00
> > > >> etc.
> > > >>
> > > >> >
> > > >> >Your assistance will be appreciated.
> > > >> >Thanks...Fraz
> > > >>
> > > >> I see no way to produce the results you requested from the tables
> > > >> given. Without CustNum on Table2 there is no way to match the two
> > > >> tables up the way your sample output specifies. Relational tables
> > > >> have no order, there is no "first", "next" or "last", so there can be
> > > >> no joining on such a basis.
> > > >>
> > > >> Please take the time to learn about data normalization. However long
> > > >> it takes to learn - and it is really quite simple - compared to the
> > > >> time you will waste trying to work with things like this it will pay
> > > >> off easily.
> > > >>
> > > >> Roy Harvey
> > > >> Beacon Falls, CT
> > > >>
> > >|||Hi Roy and Amol: Just to thank you for your valuable feedback. I got the
results I wanted. Because there were many tables with 50-100 columns, I found
the Case Statement and CROSS JOIN by Roy worked out well for my needs. Thanks
again. Fraz
"Fraz" wrote:
> Thanks Amol. Fraz
> "Amol Lembhe" wrote:
> > Hi,
> > Try this querry
> > SELECT a.custname,b.col,b.val FROM table a,
> > (
> > SELECT 'A01' as 'col', a01 as 'val' from table2
> > UNION ALL
> > SELECT 'A02' as 'col' , a02 as 'val' from table2
> > UNION ALL
> > SELECT 'A03' as 'col' , a03 as 'val' from table2
> > UNION ALL
> > SELECT 'A04' as 'col' , a04 as 'val' from table2
> > ) b
> > ORDER BY a.custname
> >
> > Amol Lembhe
> >
> >
> >
> > "Fraz" wrote:
> >
> > > It looks promising. I will use this concept to add other tables. Thanks a lot
> > > Roy and Arnie. Regards, Fraz.
> > >
> > > "Roy Harvey" wrote:
> > >
> > > > Untested, but this should do it.
> > > >
> > > > SELECT CustNum,
> > > > ColumName => > > > CASE N.I
> > > > WHEN 1 THEN 'A01'
> > > > WHEN 2 THEN 'A02'
> > > > WHEN 3 THEN 'A03'
> > > > WHEN 4 THEN 'A04'
> > > > END,
> > > > Value => > > > CASE N.I
> > > > WHEN 1 THEN A01
> > > > WHEN 2 THEN A02
> > > > WHEN 3 THEN A03
> > > > WHEN 4 THEN A04
> > > > END
> > > > FROM Table1 as A
> > > > JOIN Table2 as B
> > > > ON A.Id = B.Id
> > > > CROSS JOIN
> > > > (select 1 as I UNION ALL
> > > > select 2 as I UNION ALL
> > > > select 3 as I UNION ALL
> > > > select 4 as I) as N
> > > >
> > > > Roy Harvey
> > > > Beacon Falls, CT
> > > >
> > > >
> > > > On Tue, 11 Jul 2006 09:32:02 -0700, Fraz
> > > > <Fraz@.discussions.microsoft.com> wrote:
> > > >
> > > > >My apologies. There are PK and FK keys. Between Table1 and Table2 there is
> > > > >one-to-one relationship. Each table has 50-60 columns but I will precisely
> > > > >re-append the tables:
> > > > >Table1
> > > > >Id (PK) CustNum
> > > > >100 C041
> > > > >101 C042
> > > > >102 C043
> > > > >105 C044
> > > > >
> > > > >Table2
> > > > >ID (FK) A01 A02 A03 A04
> > > > >100 100.00 120.00 150.00 160.00
> > > > >101 200.00 210.00 250.00 260.00
> > > > >102 300.00 310.00 320.00 350.00
> > > > >105 150.00 160.00 170.00 180.00
> > > > >
> > > > >Results required by user in the output file as follows:
> > > > >CustName ColumnName Value
> > > > >C041 A01 100.00
> > > > >C041 A02 120.00
> > > > >C041 A03 150.00
> > > > >C041 A04 160.00
> > > > >C042 A01 200.00
> > > > >C042 A02 210.00
> > > > >C042 A03 250.00
> > > > >C042 A04 260.00
> > > > >C043 A01 300.00
> > > > >C043 A02 310.00
> > > > >C043 A03 320.00
> > > > >C043 A04 350.00
> > > > >C044 A01 150.00
> > > > >C044 A02 160.00
> > > > >C044 A03 170.00
> > > > >C044 A04 180.00
> > > > >
> > > > >You will notice that values in ColumnName is actually column names in Table2.
> > > > >Thanks...Fraz
> > > > >
> > > > >
> > > > >"Roy Harvey" wrote:
> > > > >
> > > > >> I have made some comments below.
> > > > >>
> > > > >> On Tue, 11 Jul 2006 05:46:02 -0700, Fraz
> > > > >> <Fraz@.discussions.microsoft.com> wrote:
> > > > >>
> > > > >> >I have two tables; Table1 and Table2 with some sample data:
> > > > >> >
> > > > >> >Table1
> > > > >> >CustNum
> > > > >> >C041
> > > > >> >C042
> > > > >> >C043
> > > > >> >C044
> > > > >> >
> > > > >> >Table2
> > > > >> >A01 A02 A03 A04
> > > > >> >100.00 120.00 150.00 160.00
> > > > >> >200.00 210.00 250.00 260.00
> > > > >> >300.00 310.00 320.00 350.00
> > > > >> >150.00 160.00 170.00 180.00
> > > > >>
> > > > >> These are ALL the rows? No key? Even denormalized tables need a key.
> > > > >>
> > > > >> >My results should look like this out of above two tables. You will see that
> > > > >> >column name A01, A02, A03 and A04 from Table2 has been shown in ColumnName as
> > > > >> >data against CustName (Table1) and values (Table2).
> > > > >> >
> > > > >> >CustName ColumnName Values
> > > > >> >C041 A01 100.00
> > > > >> >C041 A02 120.00
> > > > >> >C041 A03 150.00
> > > > >> >C041 A04 160.00
> > > > >> It appears that, somehow, a specific CustNum from Table1 (the "first")
> > > > >> is supposed to match up with the "first" row in Table2'
> > > > >>
> > > > >> >C042 A01 200.00
> > > > >> >C042 A02 210.00
> > > > >> >C042 A03 250.00
> > > > >> >C042 A04 260.00
> > > > >> Same again, but the "second"?
> > > > >>
> > > > >> >C043 A01 300.00
> > > > >> >C043 A02 310.00
> > > > >> >C043 A03 320.00
> > > > >> >C043 A04 350.00
> > > > >> etc.
> > > > >>
> > > > >> >C044 A01 150.00
> > > > >> >C044 A02 160.00
> > > > >> >C044 A03 170.00
> > > > >> >C044 A04 180.00
> > > > >> etc.
> > > > >>
> > > > >> >
> > > > >> >Your assistance will be appreciated.
> > > > >> >Thanks...Fraz
> > > > >>
> > > > >> I see no way to produce the results you requested from the tables
> > > > >> given. Without CustNum on Table2 there is no way to match the two
> > > > >> tables up the way your sample output specifies. Relational tables
> > > > >> have no order, there is no "first", "next" or "last", so there can be
> > > > >> no joining on such a basis.
> > > > >>
> > > > >> Please take the time to learn about data normalization. However long
> > > > >> it takes to learn - and it is really quite simple - compared to the
> > > > >> time you will waste trying to work with things like this it will pay
> > > > >> off easily.
> > > > >>
> > > > >> Roy Harvey
> > > > >> Beacon Falls, CT
> > > > >>
> > > >

help for query

Hello,
I have a table "log" with the following fields:

-log_id(int)
-cust_id(string)
-type(int)
-service_id(int)

type may be 0,1 or 10

I need to fetch one row for each type for each service_id for a specific
cust_id.
That is, if I have 3 different service_id's and I assume that at least
one row for each type exists with the specific cust_id, I should get 3*3
= 9 rows: no more, no less.
I prefer not to use a stored procedure.
How may I do?
Is a self-join the right way?

Thank you
Regards

--
elimina carraro per rispondere via emailYou don't appear to have any criteria for
the one row you want if there are more than one.
If that's the case and log_id is unique, this should work

select max(log_id),cust_id,type,service_id
from log
group by cust_id,type,service_id|||markc600@.hotmail.com ha scritto:
> You don't appear to have any criteria for
> the one row you want if there are more than one.
> If that's the case and log_id is unique, this should work
> select max(log_id),cust_id,type,service_id
> from log
> group by cust_id,type,service_id

It work great, thanks!

I got the same result with the following, but was much more complicated:

select *
from log a, log b
where a.cust_id='myid' and a.type IN (0,10,1) and a.log_id=b.log_id
and b.log_id IN (select top 1 log_id from log c where
c.service_id=a.service_id and c.type=a.type)
order by a.service_id

--
elimina carraro per rispondere via email

Help for INTRO function

Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>

Help for date in insert query

i want to save date using inert query like insert into tablname(field1,f2) values('jan',"& format(system.date.now,"dd/MM/yyyy hh:mm ") so to give error that char will not be converted to date and time.plz help its urgent.the same problem is with select query toooooo.

Hi,

You should elaborate more about the problem here. Post your code and let us know the way you are inserting data into the database and the fields types

Regards

|||

i want to insert in a data base some thing using this query:

insert into tble1(field1,field2) values('this some thing or data','"& syatem.now &"');

which kind of date it will store like this 24/05/2007 11:16 AM or 24/05/2007 .i want to store 24/05/2007 11:16 AM this one.i using format function of vb.net like format(system.date,"dd/MM/yyyy hh:mm tt") but it give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".in the select statement also this problem occure. i also use thhe select query as

select * from tbl1 where pdate='"& format(variabledate,"dd/MM/yyyy hh:mm tt") &"'" and it also give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".this in acess work fine but in sql server give the above error.

|||

Hi,

please try something like following

Datetime dt = newDateTime(2007,6,18,13,56,30)); //year, month, day, hour, minute, seconds

thanks,

satish.

|||

Hi there,

Use this command:

INSERT INTO TABLENAME (field1,field2) VALUES ('jan',getdate())

hope it helps you out,

gonzzas

|||

This is one of those difficult to answer questions because what you are trying to do is inheritly wrong. Datetime fields do not have a format. They represent a particular point in time. You can convert a datetime to and from a string with a specific format, but then it's no longer a datetime. You are trying to store a string with a format that sql server doesn't understand into a datetime field. SQL Server is attempting to covert your string into a real datetime so it can be stored, but the format isn't one it understands. Regardless of any of the correct formats you feed it, so long as it means the same point in time, ultimately it will get converted to the exact same number (Datetimes are internally represented by the number of days since the epoch, with the time portion being a fraction of a day).

|||

Hi again,

Here is another example of a sql command that inserts a row in a table. One of the row fields has DATETIME datatype:

Dim strAsString ="insert into users (username,password,date) values ('username1','password1',convert(datetime,'18-06-2007 23:21:00',105))"

Dim commandAsNew SqlCommand(str, conn)

Dim result = command.ExecuteScalar()

Where you see 105 it's the format chosen by me for this test. This format stores the data as dd-mm-yyyy hh:mm:ss

You can use another formats (do some light research on the subject, it should be enough) to meet your requirements: dd/mm/yyyy hh:mm:ss

gonzzas

|||

actually i solve the insert by using the query like

"insert into table tblname(f1,datef1) values('abc','"& system.datetime.now.tostring("MM/dd/yyyy hh:mm:ss") &"')"

but i still receiveing errors in select query that char conversion.

this stuff is doing in vb.net and sqlserver.

help for a SQL Query

I would like to display a query result in this way
http://xavier.bouedo.free.fr/texte.gif

in the first table, it's the result that I would like to have and in the second is the result I have

If u want to see my draft
http://xavier.bouedo.free.fr/table.gif

thank for ur help
XavierXavier,

I don't know what language you use to display your data, but in C#.Net or VB.Net I would create a dataset with two tables; one with Name (the parent)(Select distinct; if needed) and the other table with the Message (the children). Then create a relationship between the two tables in your code and use nested repeaters to display the data.

I can't really think of another way.
Does anybody have more experience with SQL to have a more elegant solution?

Does this help Xavier?
KJ.|||thanks for ur solution, I use sql server 2000.|||OK. That was a very high level discussion. Let me know if you want a few code examples. I use nested repeaters, datalists or datagrids. It all depends on the circumstances.

You basically have a typical master-detail scenario. You have to manipulate the data on the .Net side to display it the way you want. SQL server can't really do it for you. (I think!)

I use C# and SQL Server 2000.

Help for a newbie with an SQL Query

Hi,
Not sure if this is where I should post this question or not but couldn't
find anywhere else to post. Please bear with me.
I am trying to structure a simple SQL query that will return all records
whose date is less than the current system date. I've read a number of basic
tutorials, but can't seem to find how to do this. Iv'e tried using the
following statement, but get errors:
SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
sysdate()>ServiceTicketEntry.Call_Date
Can anyone give me a quick answer or point me to a good resource? Thanks in
advance.
William
You are close. You can use the ANSI standard CURRENT_TIMESTAMP or the T-SQL
specific getdate() functions to get the current date and time. That would
make your query:
SELECT ServiceTicketEntry.*
FROM ServiceTicketEntry
WHERE getdate() > ServiceTicketEntry.Call_Date
You might want to look at the DATEADD function to manipulate dates.
Remember to adjust the current-date derived functions rather than the
column-based dates. In other words, calculate a date and compare a column
to it rather than calculate each column and compar it to today's date.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"William" <William@.discussions.microsoft.com> wrote in message
news:DD4BF13D-1485-4930-B461-FBA72900EE26@.microsoft.com...
> Hi,
> Not sure if this is where I should post this question or not but couldn't
> find anywhere else to post. Please bear with me.
> I am trying to structure a simple SQL query that will return all records
> whose date is less than the current system date. I've read a number of
basic
> tutorials, but can't seem to find how to do this. Iv'e tried using the
> following statement, but get errors:
> SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
> sysdate()>ServiceTicketEntry.Call_Date
> Can anyone give me a quick answer or point me to a good resource? Thanks
in
> advance.
> William
sql

Help for a newbie with an SQL Query

Hi,
Not sure if this is where I should post this question or not but couldn't
find anywhere else to post. Please bear with me.
I am trying to structure a simple SQL query that will return all records
whose date is less than the current system date. I've read a number of basic
tutorials, but can't seem to find how to do this. Iv'e tried using the
following statement, but get errors:
SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
sysdate()>ServiceTicketEntry.Call_Date
Can anyone give me a quick answer or point me to a good resource? Thanks in
advance.
WilliamYou are close. You can use the ANSI standard CURRENT_TIMESTAMP or the T-SQL
specific getdate() functions to get the current date and time. That would
make your query:
SELECT ServiceTicketEntry.*
FROM ServiceTicketEntry
WHERE getdate() > ServiceTicketEntry.Call_Date
You might want to look at the DATEADD function to manipulate dates.
Remember to adjust the current-date derived functions rather than the
column-based dates. In other words, calculate a date and compare a column
to it rather than calculate each column and compar it to today's date.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"William" <William@.discussions.microsoft.com> wrote in message
news:DD4BF13D-1485-4930-B461-FBA72900EE26@.microsoft.com...
> Hi,
> Not sure if this is where I should post this question or not but couldn't
> find anywhere else to post. Please bear with me.
> I am trying to structure a simple SQL query that will return all records
> whose date is less than the current system date. I've read a number of
basic
> tutorials, but can't seem to find how to do this. Iv'e tried using the
> following statement, but get errors:
> SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
> sysdate()>ServiceTicketEntry.Call_Date
> Can anyone give me a quick answer or point me to a good resource? Thanks
in
> advance.
> William