May 27, 2010 at 9:19 am
Hi all,
I have this table and data:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
MACHINE_ID NVARCHAR(32),
TOOL_ID NVARCHAR(32),
ONHAND INT,
COLOR NVARCHAR(32)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, MACHINE_ID, TOOL_ID, ONHAND, COLOR)
SELECT '1','120','1234A','200','RED' UNION ALL
SELECT '2','120','2134B','300','YELLOW' UNION ALL
SELECT '3','121','64644','72','RED' UNION ALL
SELECT '4','122','74653','126','YELLOW' UNION ALL
SELECT '5','122','2134C','455','YELLOW' UNION ALL
SELECT '6','123','67857','52','RED'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
--=============================================
The result set looks like this:
ID MACHINE_ID TOOL_ID ONHAND COLOR
----------- -------------------------------- -------------------------------- ----------- --------------------
1 120 1234A 200 RED
2 120 2134B 300 YELLOW
3 121 64644 72 RED
4 122 74653 126 YELLOW
5 122 2134C 455 YELLOW
6 123 67857 52 RED
(6 row(s) affected)
I need to do something that gives me this:
MACHINE_ID [Col1] [Col2]
------------------------------------------------
120 1234A 2134B
121 64644
122 74653 2134C
123 67857
I have no idea if its even possible...
Please keep in mind that the table is going to grow, I may have more [Colx] values.
I truly appreciate any idea or suggestion!!
Thank you very much.
F
May 28, 2010 at 4:52 am
Is there any defined limit of MachineID in #mytable?
May 28, 2010 at 5:17 am
I assume the answer to my question is 2.
Try this;
Select * from (
Select a.Machine_ID, a.TOOL_ID as Col1,IsNull(b.TOOL_ID,'') as Col2, Row_number() over (partition by a.Machine_ID order by a.Machine_ID) as rno
from #mytable a
Left Outer Join #mytable b on b.ID = a.ID + 1 And a.MAchine_ID = b.Machine_ID
) Main
where RNO = 1
May 31, 2010 at 9:27 am
Hi Atif,
Thank you for your reply.
None of the columns are static, everything is dynamic, the number of machines will increase and the number of Tools will increase.
Your query works, the concept is correct but I need something that can be dynamic enough for any situation.
I am still looking, and trying, and searching!!! lol
F
May 31, 2010 at 11:46 pm
I hope you dont mind dynamic sql...?
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
MACHINE_ID NVARCHAR(32),
TOOL_ID NVARCHAR(32),
ONHAND INT,
COLOR NVARCHAR(32)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, MACHINE_ID, TOOL_ID, ONHAND, COLOR)
SELECT '1','120','1234A','200','RED' UNION ALL
SELECT '2','120','2134B','300','YELLOW' UNION ALL
SELECT '3','121','64644','72','RED' UNION ALL
SELECT '4','122','74653','126','YELLOW' UNION ALL
SELECT '5','122','2134C','455','YELLOW' UNION ALL
SELECT '6','123','67857','52','RED' UNION ALL
SELECT '7','123','67858','52','RED' UNION ALL
SELECT '8','123','67859','52','RED'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
Declare @vCols varchar(max)
Declare @vJoins varchar(max)
Declare @vCntTools int
DECLARE @query NVARCHAR(4000)
Select @vCntTools = Max(Cnt)
from (
Select Count(*) Cnt
from #mytable
group by MAchine_ID
) Data
Select @vCols = '',@vJoins = ''
;with wcte as (
Select Top (@vCntTools) column_id, row_number() over (order by column_id) N
from sys.columns
)Select @vCols = @vCols + ',IsNull(' + Char(64 + N) + '.Tool_ID,'''') as [Col' + Cast(N as varchar(10)) + ']'
,@vJoins = @vJoins + ' Left Outer Join #mytable ' + Char(65 + N) + ' on ' + Char(65 + N) + '.ID = ' + Char(65 + N-1) + '.ID + 1 And ' + Char(65 + N) + '.Machine_ID = ' + Char(65 + N-1) + '.Machine_ID'
from wcte
where N <= @vCntTools
Select @vCols = Stuff(@vCols,1,1,'')
Set @query = 'Select * from (
Select a.Machine_ID,' + @vCols + ' , Row_number() over (partition by a.Machine_ID order by a.Machine_ID) as rno
from #mytable a ' + IsNull(@vJoins,'') + ' ) Data Where rno = 1'
Print @query
Exec(@query)
Drop Table #mytable
June 2, 2010 at 8:06 am
Thank you very much Atif...
That helped a lot!!!!
June 3, 2010 at 2:35 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply