Prev: Table relationship
Next: Using COM DLLs from SQL
From: chintu4uin on 7 Jul 2010 04:01
Hello friends I need help for the below query. My table structure is
as such
-------------------------------------------------------------------------------
CatCode | GradeCode | ItemCode | HeatCode | SizeLot
-------------------------------------------------------------------------------
01 | A105 | A | H1 | S1
01 | A105 | A | H2 | S1
01 | 0304 | A | H3 | S2
01 | 0LF2 | A | H4 | S4
02 | A105 | B | H1 | S1
02 | A105 | B | H2 | S1
02 | A266 | C | H3 | S2
02 | F304 | C | H4 | S4


CREATE TABLE [dbo].[Tmp_ItemBatchDetails](
[CatCode] [varchar](2),
[GradeCode] [varchar](10),
[ItemCode] [varchar](20),
[HeatCode] [varchar](12),
[SizeLot] [varchar](20)
)


insert into Tmp_ItemBatchDetails values('01','A105','A','H1','S1')
insert into Tmp_ItemBatchDetails values('01','A105','A','H2','S1')
insert into Tmp_ItemBatchDetails values('01','0304','A','H3','S2')
insert into Tmp_ItemBatchDetails values('01','0LF2','A','H4','S4')
insert into Tmp_ItemBatchDetails values('02','A105','B','H1','S1')
insert into Tmp_ItemBatchDetails values('02','A105','B','H2','S1')
insert into Tmp_ItemBatchDetails values('02','A266','C','H3','S2')
insert into Tmp_ItemBatchDetails values('02','F304','C','H4','S4')


My result should be as such
-------------------------------------------------------------------------------
CatCode | GradeCode | ItemCode | HeatCode | SizeLot
-------------------------------------------------------------------------------
01 | A105 | A | H1 | S1
01 | A105 | A | H2 | S1
01 | 0304 | A | H3 | S2
01 | 0LF2 | A | H4 | S4
01 | A266 | Null | Null |
Null
01 | F304 | Null | Null |
Null
02 | A105 | B | H1 | S1
02 | A105 | B | H2 | S1
02 | A266 | C | H3 | S2
02 | F304 | C | H4 | S4
02 | 0304 | Null | Null |
Null
02 | 0LF2 | Null | Null |
Null

Please help for the same.
From: Erland Sommarskog on 7 Jul 2010 04:41
chintu4uin(a)gmail.com (chintu4uin(a)gmail.com) writes:
> Hello friends I need help for the below query. My table structure is
> as such
>...
>
> My result should be as such
>...

Thanks for the CREATE TABLE statement, the INSERT data and the desired
result, but there is still a thing missing: why do you want this particular
result? Below is a query that gives the desired result, save the order,
but it is not clear to me whether you desire a certain order and what in
such case is based on. Also, I like to stress since I know nothing about
the business rules, it may not work with your real-world data.

Here is the query (the syntax requires SQL 2005):

WITH CatCodes AS (
SELECT DISTINCT CatCode
FROM Tmp_ItemBatchDetails
),
GradeCodes AS (
SELECT DISTINCT GradeCode
FROM Tmp_ItemBatchDetails
)
SELECT C.CatCode, G.GradeCode, BD.ItemCode, BD.HeatCode, BD.SizeLot
FROM CatCodes C
CROSS JOIN GradeCodes G
LEFT JOIN Tmp_ItemBatchDetails BD ON C.CatCode = BD.CatCode
AND G.GradeCode = BD.GradeCode
ORDER BY C.CatCode, G.GradeCode

A final note is that I would expect the CatCodes and GradeCodes to be
defined in some other table; in such case they should be used rather
than the CTEs in this query.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: chintu4uin on 9 Jul 2010 02:52
Hi Erland Sommarskog
Thnanks u r query work for me.

On Jul 7, 1:41 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> chintu4...(a)gmail.com (chintu4...(a)gmail.com) writes:
> > Hello  friends I need help for the below query. My table structure is
> > as such
> >...
>
> > My result should be as such
> >...
>
> Thanks for the CREATE TABLE statement, the INSERT data and the desired
> result, but there is still a thing missing: why do you want this particular
> result? Below is a query that gives the desired result, save the order,
> but it is not clear to me whether you desire a certain order and what in
> such case is based on. Also, I like to stress since I know nothing about
> the business rules, it may not work with your real-world data.
>
> Here is the query (the syntax requires SQL 2005):
>
> WITH CatCodes AS (
>    SELECT DISTINCT CatCode
>    FROM   Tmp_ItemBatchDetails
> ),
>   GradeCodes AS (
>    SELECT DISTINCT GradeCode
>    FROM   Tmp_ItemBatchDetails
> )
> SELECT C.CatCode, G.GradeCode, BD.ItemCode, BD.HeatCode, BD.SizeLot
> FROM   CatCodes C
> CROSS  JOIN GradeCodes G
> LEFT   JOIN Tmp_ItemBatchDetails BD ON C.CatCode = BD.CatCode
>                                    AND G.GradeCode = BD.GradeCode
> ORDER BY C.CatCode, G.GradeCode
>
> A final note is that I would expect the CatCodes and GradeCodes to be
> defined in some other table; in such case they should be used rather
> than the CTEs in this query.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Table relationship
Next: Using COM DLLs from SQL