Click here to Skip to main content
14,920,295 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have 3 tables as follows

Table1:
UnitTypeId  UnitType
1            Rolls
2           Sheets
3           Bobbins

Table2:
AttributeId	Attribute
1	        Width
2	        Diameter
3	        Comments
4	        Core Size
5	        Length
6	       Diameter Type
7	       Core Type
8	       Grain_Direction

Table3:
UnitTypeAttributeId	UnitTypeId	AttributeId	DisplayAs
1	                   1	           1	         Roll Width
2	                   1	           2             Roll Diameter
3	                   1	           3	         Roll Dia comments
4	                   1	           4             Core size
5	                   2	           1             Sheet width
6	                   2	           5	         Sheet Length
7	                   3	           1             Bobbin width
8	                   3	           5             Bobbin Length
9	                   1	           6	         Diameter Type
10	                   1	           7	         Core Type
11	                   2	           8	         Grain Dir

And i want the output as follows

UnitType     Attribute1         attribute2        attribute3         attribute4      attribute5
Rolls        Roll width          diameter         core size         core typr   diametercmnts
sheets       sheet width          sheet length    M weight          grain direction
Bobbins      bobbins width        bobbins length
Boxes       Boxes width          Boxes  length
Cores        Cores  width        Cores  length   core inner dimension core wall thickness core cut types
Posted
Updated 3-Jun-13 10:12am
v4
Comments
gvprabu 31-May-13 4:52am
   
you have the attribute list up to 1 to 5 columns or more than that also there
Member 9848727 31-May-13 4:54am
   
more than also i have upto 6
Arun-23 31-May-13 7:59am
   
what have u tried?? to sole this..
Member 9848727 31-May-13 8:14am
   
I got the solution
DECLARE @Table TABLE(UnitTypeID INT,UnitType VARCHAR(MAX),Attribute1 VARCHAR(MAX),Attribute2 VARCHAR(MAX) ,Attribute3 VARCHAR(MAX),Attribute4 VARCHAR(MAX),
Attribute5 VARCHAR(MAX),Attribute6 VARCHAR(MAX) ,Attribute7 VARCHAR(MAX),Attribute8 VARCHAR(MAX))

INSERT INTO @Table
SELECT
UnitTypeID
,UnitType
,[1] AS Attribute1
,[2] AS Attribute2
,[3] AS Attribute3
,[4] AS Attribute4
,[5] AS Attribute5
,[6] AS Attribute6
,[7] AS Attribute7
,[8] AS Attribute8

FROM
( SELECT UM.UnitTypeId,ROW_NUMBER()OVER(PARTITION BY UnitType ORDER BY Displayas) as AttributeId,UnitType, DisplayAs
FROM
dbo.UnitTypeMaster UM
INNER JOIN
dbo.UnitType_Attributes UA ON UM.UnitTypeId = UA.UnitTypeId

WHERE ISNULL(um.ActiveYN,1) =1
) AS TempTABLE
PIVOT
(
MAX(DisplayAs) FOR AttributeId IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS PivoTable ORDER BY UnitTypeID ASC

--SELECT * FROM @Table
DECLARE @TableStatus TABLE (UnitTypeId INT)

INSERT INTO @TableStatus

SELECT DISTINCT
UA.UnitTypeId
FROM
om_order_items_details ID
INNER JOIN
UnitType_Attributes UA ON UA.UnitTypeId = ID.UnitTypeId

SELECT DISTINCT
t.UnitTypeID
,UnitType
,Attribute1
,Attribute2
,Attribute3
,Attribute4
,Attribute5
,Attribute6
,Attribute7
,Attribute8
,CASE WHEN ts1.unittypeid = ua.unittypeid THEN 'Y' ELSE 'N' END AS 'Status'
FROM
@Table T
LEFT JOIN
@TableStatus TS1 ON TS1.UnitTypeId = T.UnitTypeID
LEFT JOIN
UnitType_Attributes UA ON UA.UnitTypeId = TS1.UnitTypeId

1 solution

Try this:
SQL
DECLARE @tab1 TABLE(UnitTypeId INT, UnitType NVARCHAR(30))

INSERT INTO @tab1 (UnitTypeId, UnitType)
VALUES(1, 'Rolls')
INSERT INTO @tab1 (UnitTypeId, UnitType)
VALUES(2, 'Sheets')
INSERT INTO @tab1 (UnitTypeId, UnitType)
VALUES(3, 'Bobbins')

DECLARE @tab2 TABLE(AttributeId INT, Attribute NVARCHAR(30))

INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(1, 'Width')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(2, 'Diameter')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(3, 'Comments')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(4, 'Core Size')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(5, 'Length')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(6, 'Diameter Type')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(7, 'Core Type')
INSERT INTO @tab2 (AttributeId, Attribute)
VALUES(8, 'Grain_Direction')

DECLARE @tab3 TABLE (UnitTypeAttributeId INT, UnitTypeId INT, AttributeId INT, DisplayAs NVARCHAR(30))

INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(1, 1, 1, 'Roll Width')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(2, 1, 2, 'Roll Diameter')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(3, 1, 3, 'Roll Dia comments')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(4, 1, 4, 'Core size')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(5, 2, 1, 'Sheet width')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(6, 2, 5, 'Sheet Length')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(7, 3, 1, 'Bobbin width')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(8, 3, 5, 'Bobbin Length')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(9, 1, 6, 'Diameter Type')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(10, 1, 7, 'Core Type')
INSERT INTO @tab3 (UnitTypeAttributeId, UnitTypeId, AttributeId, DisplayAs)
VALUES(11, 2, 8, 'Grain Dir')


SELECT UnitType, [Width], [Diameter], [Comments], [Core Size], [Length], [Diameter Type], [Core Type], [Grain_Direction]
FROM (
	SELECT t2.UnitType, t3.Attribute, t1.DisplayAs
	FROM @tab3 AS t1 INNER JOIN @tab1 AS t2 ON t1.UnitTypeId = t2.UnitTypeId
		INNER JOIN @tab2 AS t3 ON t1.AttributeId = t3.AttributeId
	) AS DT
PIVOT(MAX(DisplayAs) FOR Attribute IN ([Width], [Diameter], [Comments], [Core Size], [Length], [Diameter Type], [Core Type], [Grain_Direction])) AS PT
   
Comments
gvprabu 4-Jun-13 6:37am
   
my 5+ :-)
Maciej Los 4-Jun-13 6:48am
   
Thank you ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900