February 2, 2017 at 2:56 am
Hi Team,
I got a scenario : Got two tables
Table 1:
ID(col1) Number(col2) Description(col3)
1(col1) 000101(col2) xyz(col3)
2(col1) 000201(col2) abc(col3)
Table 2:
ID(col1) Grade(col2) Value(col3)
1(col1) aa(col2) 10(col3)
1(col1) bb(col2) 20(col3)
1(col1) cc(col2) 30(col3)
2(col1) aa(col2) 11(col3)
2(col1) bb(col2) 21(col3)
2(col1) cc(col2) 31(col3)
Output should be like
Number(col1) Description(col2) Grade1_Value(col3) Grade2_value(col4) Grade3_value(col5)
000101(col1) xyz(col2) 10(col3) 20(col4) 30(col5)
000201(col1) abc(col2) 11(col3) 21(col4) 31(col5)
Please mind the structures....Appreciate any help.
Thank You...
February 2, 2017 at 3:11 am
let's start off by making that into something usable for other members of the community. Have a read of the link in my signature, however, I've done it for you this time. it really helps you get quicker answer from others, and they are more likely to answer, if they don't need to create your data, and make some guesses at the data types:
CREATE TABLE #Table1 (ID INT,
Number VARCHAR(6),
[Description] VARCHAR(3));
GO
CREATE TABLE #Table2 (ID INT,
Grade VARCHAR(2),
[Value] INT);
GO
INSERT INTO #Table1
VALUES (1, '000101', 'xyz'),
(2, '000201', 'abc');
GO
INSERT INTO #Table2
VALUES (1, 'aa', 10),
(1, 'bb', 20),
(1, 'cc', 30),
(2, 'aa', 11),
(2, 'bb', 21),
(2, 'cc', 31);
GO
SELECT *
FROM #Table1;
SELECT *
FROM #Table2;
--Expected Output
SELECT '000101' AS Number, 'xyz' as Description, 10 AS Grade1_value, 20 AS Grade2_value, 30 AS Grade3_value
UNION
SELECT '000201' AS Number, 'abc' as Description, 11 AS Grade1_value, 21 AS Grade2_value, 31 AS Grade3_value;
GO
DROP TABLE #Table1;
DROP TABLE #Table2;
GO
Question, will there only ever be 3 grades? Could there be a chance that someone has 4 grades? You example has both ID's only having 3. is that definitive?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 2, 2017 at 3:14 am
I would leave that to your presentation layer, if I were you. Excel and SSRS will both present the data in the form you're looking for very easily. What happens if you have more than three rows for any value of ID in Table 2, by the way?
John
February 2, 2017 at 3:31 am
This is totally dependant on your data, but as we have such a small sample, then who knows. As John said though, you're better off doing it in a presentation layer. If you have a variable number of grades, etc, then you'll want a matrix in SSRs, or a Pivot Table in Excel.
This works for the sample data I created for you, but I wouldn't be surprised if it doesn't for your actual data:SELECT T1.Number, T1.[Description],
Pvt.aa AS Grade1_value, Pvt.bb As Grade2_value, PVt.cc AS Grade3_value
FROM (SELECT ID, Grade, Value
FROM #Table2) AS T2
PIVOT ( AVG(Value)
FOR Grade IN ([aa], [bb], [cc])
) AS Pvt
JOIN #Table1 T1 ON Pvt.ID = T1.ID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 2, 2017 at 5:40 am
Noted with thanks Thom...Please check below the statements:
--TABLE 1
CREATE TABLE [dbo].[Table1](
[ID] [int] NULL,
[Number] [INT] NULL,
[Description] [varchar](20) NULL
) ;
GO
--TABLE 2
CREATE TABLE [dbo].[Table2](
[ID] [int] NULL,
[Grade] [varchar] (10) NULL,
[Value] [int] NULL
);
GO
-- OUT PUT TABLE, OUTPUT SHOULD LOOK LIKE IN THE TABLE
CREATE TABLE [dbo].[OUTPUTTable](
[ID] [int] NULL,
[Number] [INT] NULL,
[Description] [varchar](20) NULL,
[Grade_length] [int] NULL,
[Grade_width] [int] NULL
) ;
GO
INSERT INTO Table1
VALUES (111, 200022, 'ABC'),
(222, 200023, 'BCD'),
(333, 200025, 'CDE');
GO
--0000000822 - Length Identifier, 0000000823 - Width Identifier
INSERT INTO Table2
VALUES (111,'0000000822',1200),
(111,'0000000823',600),
(222,'0000000822',2000),
(222,'0000000823',800),
(333,'0000000822',4000),
(333,'0000000823',900);
GO
INSERT INTO OUTPUTTable
VALUES (111, 200022, 'ABC', 1200, 600),
(222, 200023, 'BCD', 2000, 800),
(333, 200025, 'CDE', 4000, 900);
there are many grades in that table but i need only two of them but they are defined as identifiers as shown above.
Thank You..
February 2, 2017 at 6:44 am
I tried like
SELECT T1.ID,T1.NUMBER,T1.DESCRIPTION,
CASE WHEN T2.GRADE='0000000822' THEN T2.VALUE END AS GRADE_LENGTH,
CASE WHEN T2.GRADE='0000000823' THEN T2.VALUE END AS GRADE_WIDTH
FROM [dbo].[Table1] T1 join [dbo].[Table2] T2 on T1.ID=T2.ID
but i get two rows for each record, i wanted in single line.
February 2, 2017 at 7:25 am
sqlquery29 - Thursday, February 2, 2017 6:44 AMI tried like
SELECT T1.ID,T1.NUMBER,T1.DESCRIPTION,
CASE WHEN T2.GRADE='0000000822' THEN T2.VALUE END AS GRADE_LENGTH,
CASE WHEN T2.GRADE='0000000823' THEN T2.VALUE END AS GRADE_WIDTH
FROM [dbo].[Table1] T1 join [dbo].[Table2] T2 on T1.ID=T2.IDbut i get two rows for each record, i wanted in single line.
That's to be expected, as T1's ID appears more than once in T2.
Again, consider using PIVOT, or even easier in this case, make two joins, on on GRADE 0000000822 and the other on GRADE 0000000823.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 3, 2017 at 3:02 am
This is Working :
select T1.ID,T1.Number,T1.Description,piv.[0000000822] AS GRADE_LENGTH,piv.[0000000823] AS GRADE_WIDTH
from [dbo].[Table1] T1 JOIN
(
select [ID],[Grade],[Value]
from [dbo].[Table2]
) T2
pivot
(
sum([Value])
for [Grade] in ([0000000822], [0000000823])
) piv
ON T1.ID=piv.ID;
Can i do it without using PIVOT ?
February 3, 2017 at 3:08 am
sqlquery29 - Friday, February 3, 2017 3:02 AMCan i do it without using PIVOT ?
Yes, as your Width and Length have defined values:SELECT T1.ID,T1.NUMBER,T1.DESCRIPTION,
T2L.Value AS GRADE_LENGTH,
T2W.Value AS GRADE_WIDTH
FROM [dbo].[Table1] T1
JOIN [dbo].[Table2] T2L ON T1.ID=T2L.ID AND T2L.GRADE = '0000000822'
JOIN [dbo].[Table2] T2W ON T1.ID=T2W.ID AND T2W.GRADE = '0000000823';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply