June 18, 2009 at 2:48 am
Hi friends
I have table tCustomer columns Id and ImageName
The Data is like
Id Customerid ImageName
1 123 123_X.jpg
2 123 123_Y.Jpg
3 456 456_X.jpg
4 456 456_Y.jpg
I want OutPut like below data
Customerid Ximage YImage
123 123_X.jpg 123_Y.jpg
456 456_X.jpg 456_Y.jpg
please help me.
Regards
Swamy.
June 18, 2009 at 3:26 am
Will there always just be two image types, X and Y? If so are they always named like '_X.jpg' and '_Y.jpg'?
June 18, 2009 at 3:31 am
Have you tried searching around here for Pivot and or Cross-tab queries?
If you can't find anything let me know.
Also please answer the question above as this will determine weather it needs to be dynamic or not
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 18, 2009 at 3:32 am
yes there are always two image types so many customers.
June 18, 2009 at 3:33 am
yes the rows are dynamic , but image types should be fixed.
June 18, 2009 at 3:40 am
have you looked up Pivot/cross-tab queries?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 18, 2009 at 3:43 am
yes i am not getting.
Please help me.
Regards
Swamy.
June 18, 2009 at 3:44 am
Still not sure if it's dynamic or not...? does RIGHT(ImageName,6) always equal either '_X.jpg' or '_Y.jpg'?
Very good article on pivots found at http://www.sqlservercentral.com/articles/T-SQL/63681/
June 18, 2009 at 4:51 am
Swamy Magam (6/18/2009)
Hi friendsI have table tCustomer columns Id and ImageName
The Data is like
Id Customerid ImageName
1 123 123_X.jpg
2 123 123_Y.Jpg
3 456 456_X.jpg
4 456 456_Y.jpg
I want OutPut like below data
Customerid Ximage YImage
123 123_X.jpg 123_Y.jpg
456 456_X.jpg 456_Y.jpg
please help me.
Regards
Swamy.
declare @colname varchar(100)
declare @rowcolname varchar(1000)
set @rowcolname = ''
declare col CURSOR FOR
select id from actionstate
open col
fetch next from col into @colname
while @@fetch_status = 0
begin
set @rowcolname = @rowcolname + '[' + @colname + '] int,'
fetch next from col into @colname
end
close col
deallocate col
print('Create TAble tablename('+ @rowcolname + ')')
exec('Create TAble tablename('+ @rowcolname + ')')
select * from tablename
drop tablename
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
June 18, 2009 at 5:01 am
Here is a solution that will work for Multiple Image types:
CREATE TABLE MyTable
(Id INT,
CustomerId INT,
ImageName VARCHAR(100))
INSERT INTO MyTable
SELECT 1,123,'123_X.jpg' UNION ALL
SELECT 2,123,'123_Y.jpg' UNION ALL
SELECT 3,456,'456_X.jpg' UNION ALL
SELECT 4,456,'456_Y.jpg'
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT SUBSTRING(REVERSE(ImageName),5,1) + 'Image'
FROM MyTable
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT
CustomerId,
ImageName,
SUBSTRING(REVERSE(ImageName),5,1) + ''Image'' as Header
FROM MyTable
) t
PIVOT (MIN(ImageName) FOR Header IN (' + @ColsList + ')) PVT')
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 18, 2009 at 5:11 am
Christopher Stobbs (6/18/2009)
Here is a solution that will work for Multiple Image types:
CREATE TABLE MyTable
(Id INT,
CustomerId INT,
ImageName VARCHAR(100))
INSERT INTO MyTable
SELECT 1,123,'123_X.jpg' UNION ALL
SELECT 2,123,'123_Y.jpg' UNION ALL
SELECT 3,456,'456_X.jpg' UNION ALL
SELECT 4,456,'456_Y.jpg'
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT SUBSTRING(REVERSE(ImageName),5,1) + 'Image'
FROM MyTable
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT
CustomerId,
ImageName,
SUBSTRING(REVERSE(ImageName),5,1) + ''Image'' as Header
FROM MyTable
) t
PIVOT (MIN(ImageName) FOR Header IN (' + @ColsList + ')) PVT')
Great solution Christopher, I hate cursors!
June 18, 2009 at 5:13 am
me too 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply