I want the ouput as follows

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

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 30-May-13 20:51pm

Member 9848727

Updated 3-Jun-13 10:12am

Maciej Los

v4

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-06-03T10:33:00

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