help required for select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help required for select query

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-06 : 08:20:46

[code]
TABLE1
INC (INTEGER)
1
2
3
4
5

TABLE2
INC - FOREIGN KEY references Table1
TYP - INTEGER
DESC_COL - text

INC TYP DESC_COL
1 1 LOOK
1 2 ABC
1 3 ASDFASDF
1 4 Manual entry will be
1 4 journal posted
1 5 Need to look
3 2 The
3 3 The batch
3 4 We have been
3 4 The DBA team
3 5 We are working

Expected output:
INC TYP-1 TYP-2 TYP-3 TYP-4 TYP-5
1 LOOK ABC ASDFASDF Manual entry will be¬¬journal posted Need to look
2 NULL NULL NULL NULL NULL
3 NULL The The batch We have been¬¬The DBA team We are working
4 NULL NULL NULL NULL NULL
5 NULL NULL NULL NULL NULL

[/code]
I tried using following 2 queries, but i m not getting the expected output

[code]

select
INC,
stuff((
select CONVERT(varchar(500),DESC_COL) + '¬¬'
from TABLE2 ir2
where ir1.INC = ir2.INC
order by ir1.INC
for xml path('')
),1,1,'') as DESC_COL
from TABLE2 ir1

SELECT
T1.INC
,CONVERT(varchar(500),fld1.DESC_COL) as TYP-1
,CONVERT(varchar(500),fld2.DESC_COL) as TYP-2
,CONVERT(varchar(500),fld3.DESC_COL) as TYP-3
,CONVERT(varchar(500),fld4.DESC_COL) as TYP-4
,CONVERT(varchar(500),fld5.DESC_COL) as TYP-5
from TABLE1 T1
inner join TABLE2 fld1 on T1.INC = fld1.INC and fld1.TYP = 1
inner join TABLE2 fld2 on T1.INC = fld2.INC and fld2.TYP = 2
inner join TABLE2 fld3 on T1.INC = fld3.INC and fld3.TYP = 3
inner join TABLE2 fld4 on T1.INC = fld4.INC and fld4.TYP = 4
inner join TABLE2 fld4 on T1.INC = fld4.INC and fld4.TYP = 5

[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 08:42:32

[code]DECLARE @TABLE1 TABLE
(
INC INTEGER
)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

DECLARE @TABLE2 TABLE
(
INC int,
TYP int,
DESC_COL varchar(100)
)

INSERT INTO @TABLE2
SELECT 1, 1, 'LOOK' UNION ALL
SELECT 1, 2, 'ABC' UNION ALL
SELECT 1, 3, 'ASDFASDF' UNION ALL
SELECT 1, 4, 'Manual entry will be' UNION ALL
SELECT 1, 4, 'journal posted' UNION ALL
SELECT 1, 5, 'Need to look ' UNION ALL
SELECT 3, 2, 'The' UNION ALL
SELECT 3, 3, 'The batch ' UNION ALL
SELECT 3, 4, 'We have been ' UNION ALL
SELECT 3, 4, 'The DBA team ' UNION ALL
SELECT 3, 5, 'We are working'

;WITH data(INC, TYP, DESC_COL)
AS
(
SELECT t1.INC, t2.TYP,
DESC_COL = STUFF((SELECT DISTINCT TOP 100 PERCENT ' ' + x.DESC_COL
FROM @TABLE2 AS x WHERE x.INC = t1.INC AND x.TYP = t2.TYP
ORDER BY ' ' + x.DESC_COL FOR XML PATH('')), 1, 1, '')
FROM @TABLE1 t1
LEFT JOIN @TABLE2 t2 ON t1.INC = t2.INC
GROUP BY t1.INC, t2.TYP
)
SELECT INC, [1] AS [TYP-1], [2] AS [TYP-2], [3] AS [TYP-3], [4] AS [TYP-4], [5] AS [TYP-5]
FROM data
pivot
(
MAX(DESC_COL)
FOR TYP IN ([1], [2], [3], [4], [5])
) p

/*
INC TYP-1 TYP-2 TYP-3 TYP-4 TYP-5
----------- -------- ------- ----------- ----------------------------------- ----------------
1 LOOK ABC ASDFASDF Manual entry will be journal posted Need to look
2 NULL NULL NULL NULL NULL
3 NULL The The batch The DBA team We have been We are working
4 NULL NULL NULL NULL NULL
5 NULL NULL NULL NULL NULL

(5 row(s) affected)
*/[/code]

[edit] : re-format the query a bit

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:40:51

[code]SELECT t1.INC,
MAX(CASE WHEN t2.TYP=1 THEN LEFT(dl.desclist,LEN(dl.desclist)-1) ELSE NULL END) AS [TYP-1],
MAX(CASE WHEN t2.TYP=1 THEN LEFT(dl.desclist,LEN(dl.desclist)-1) ELSE NULL END) AS [TYP-2],
MAX(CASE WHEN t2.TYP=1 THEN LEFT(dl.desclist,LEN(dl.desclist)-1) ELSE NULL END) AS [TYP-3],
MAX(CASE WHEN t2.TYP=1 THEN LEFT(dl.desclist,LEN(dl.desclist)-1) ELSE NULL END) AS [TYP-4],
MAX(CASE WHEN t2.TYP=1 THEN LEFT(dl.desclist,LEN(dl.desclist)-1) ELSE NULL END) AS [TYP-5]
FROM TABLE1 t1
LEFT JOIN
(SELECT DISTINCT INC,TYP FROM TABLE2) t2
ON t2.INC =t1.INC
CROSS APPLY (SELECT DESC_COL+ '~~' AS [text()]
FROM TABLE2
WHERE TYP=t2.TYP
FOR XML PATH(''))dl(desclist)
GROUP BY t1.INC[/code]

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-06 : 23:24:43

hi khtan,
i used your query... but there is an issue... DESC_COL is actually TEXT, so i need to convert it into varchar.
"The data types varchar and text are incompatible in the add operator."
when i try to use "CONVERT(varchar(500),DESC_COL)" in "WITH data(INC, TYP, DESC_COL)", it gives an error...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 23:31:53

[code]DECLARE @TABLE1 TABLE
(
INC INTEGER
)
INSERT INTO @TABLE1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

DECLARE @TABLE2 TABLE
(
INC int,
TYP int,
DESC_COL text
)

INSERT INTO @TABLE2
SELECT 1, 1, 'LOOK' UNION ALL
SELECT 1, 2, 'ABC' UNION ALL
SELECT 1, 3, 'ASDFASDF' UNION ALL
SELECT 1, 4, 'Manual entry will be' UNION ALL
SELECT 1, 4, 'journal posted' UNION ALL
SELECT 1, 5, 'Need to look ' UNION ALL
SELECT 3, 2, 'The' UNION ALL
SELECT 3, 3, 'The batch ' UNION ALL
SELECT 3, 4, 'We have been ' UNION ALL
SELECT 3, 4, 'The DBA team ' UNION ALL
SELECT 3, 5, 'We are working'

;WITH data(INC, TYP, DESC_COL)
AS
(
SELECT t1.INC, t2.TYP,
DESC_COL = STUFF((SELECT DISTINCT TOP 100 PERCENT ' ' + CONVERT(varchar(MAX), x.DESC_COL)
FROM @TABLE2 AS x WHERE x.INC = t1.INC AND x.TYP = t2.TYP
ORDER BY ' ' + CONVERT(varchar(MAX), x.DESC_COL) FOR XML PATH('')), 1, 1, '')
FROM @TABLE1 t1
LEFT JOIN @TABLE2 t2 ON t1.INC = t2.INC
GROUP BY t1.INC, t2.TYP
)
SELECT INC, [1] AS [TYP-1], [2] AS [TYP-2], [3] AS [TYP-3], [4] AS [TYP-4], [5] AS [TYP-5]
FROM data
pivot
(
MAX(DESC_COL)
FOR TYP IN ([1], [2], [3], [4], [5])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-07 : 02:34:01

thnx khtan

... query works perfectly... just a small issue...


DECLARE @TABLE1 TABLE
(
	INC INTEGER
)
INSERT INTO @TABLE1
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4 UNION ALL
SELECT	5	

DECLARE @TABLE2 TABLE
(
	IDEN		int,
	INC 		int,
	TYP 		int,
	DESC_COL 	text
)

INSERT INTO @TABLE2
SELECT	1,	1,	1,	'LOOK'			UNION ALL
SELECT	2,	1,	2,	'ABC'			UNION ALL
SELECT	3,	1,	3,	'ASDFASDF'		UNION ALL
SELECT	4,	1,	4,	'Manual entry will be'	UNION ALL
SELECT	5,	1,	4,	'journal posted'	UNION ALL
SELECT	6,	1,	5,	'Need to look '		UNION ALL
SELECT	7,	3,	2,	'The'    		UNION ALL
SELECT	8,	3,	3,	'The batch '		UNION ALL
SELECT	9,	3,	4,	'We have been '		UNION ALL
SELECT 10,	3,	4,	'The DBA team '		UNION ALL
SELECT 11,	3,	5,	'We are working' 

;WITH data(INC, TYP, DESC_COL)
AS
(
	SELECT	t1.INC, t2.TYP, 	
		DESC_COL = STUFF((SELECT DISTINCT TOP 100 PERCENT CONVERT(varchar(MAX), IDEN) + ' ' + CONVERT(varchar(MAX), x.DESC_COL) + '¬¬¬'
                    FROM @TABLE2 AS x WHERE x.INC = t1.INC AND x.TYP = t2.TYP
                    ORDER BY  CONVERT(varchar(MAX), IDEN) + ' ' + CONVERT(varchar(MAX), x.DESC_COL) + '¬¬¬'  desc
					FOR XML PATH('')), 1, 1, '') 
        FROM	@TABLE1 t1
		LEFT JOIN @TABLE2 t2	ON	t1.INC	= t2.INC
	GROUP BY t1.INC, t2.TYP
)
SELECT	INC, [1] AS [TYP-1], [2] AS [TYP-2], [3] AS [TYP-3], [4] AS [TYP-4], [5] AS [TYP-5]
FROM	data
	pivot
	(
		MAX(DESC_COL)
		FOR TYP IN ([1], [2], [3], [4], [5])
	) p

i have added one identity column & use it to display the data in the descending order, but i dont want this identity column to be displayed. if i remove it, i get the error - "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 02:54:40

Are you using DISTINCT?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 02:56:44


DECLARE @TABLE1 TABLE
(
	INC INTEGER
)
INSERT INTO @TABLE1
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4 UNION ALL
SELECT	5	

DECLARE @TABLE2 TABLE
(
	IDEN		int,
	INC 		int,
	TYP 		int,
	DESC_COL 	text
)

INSERT INTO @TABLE2
SELECT	1,	1,	1,	'LOOK'			UNION ALL
SELECT	2,	1,	2,	'ABC'			UNION ALL
SELECT	3,	1,	3,	'ASDFASDF'		UNION ALL
SELECT	4,	1,	4,	'Manual entry will be'	UNION ALL
SELECT	5,	1,	4,	'journal posted'	UNION ALL
SELECT	6,	1,	5,	'Need to look '		UNION ALL
SELECT	7,	3,	2,	'The'    		UNION ALL
SELECT	8,	3,	3,	'The batch '		UNION ALL
SELECT	9,	3,	4,	'We have been '		UNION ALL
SELECT 10,	3,	4,	'The DBA team '		UNION ALL
SELECT 11,	3,	5,	'We are working' 

;WITH data(INC, TYP, DESC_COL)
AS
(
	SELECT	t1.INC, t2.TYP, 	
		DESC_COL = STUFF((SELECT ' ' + CONVERT(varchar(MAX), x.DESC_COL) + '¬¬¬'
                    FROM @TABLE2 AS x WHERE x.INC = t1.INC AND x.TYP = t2.TYP
                    ORDER BY IDEN desc
					FOR XML PATH('')), 1, 1, '') 
        FROM	@TABLE1 t1
		LEFT JOIN @TABLE2 t2	ON	t1.INC	= t2.INC
	GROUP BY t1.INC, t2.TYP
)
SELECT	INC, [1] AS [TYP-1], [2] AS [TYP-2], [3] AS [TYP-3], [4] AS [TYP-4], [5] AS [TYP-5]
FROM	data
	pivot
	(
		MAX(DESC_COL)
		FOR TYP IN ([1], [2], [3], [4], [5])
	) p

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-07 : 02:57:51

quote:
descending order

Descending order ?

you will get "journal posted Manual entry will be". This is what you want ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-07 : 03:33:16

Help required?
Shouldn't it be "help wanted" since it is free?

E 12°55'05.25"
N 56°04'39.16"

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-07 : 04:43:00

quote:
Originally posted by khtan

quote:
descending order

Descending order ?

you will get "journal posted Manual entry will be". This is what you want ?

KH
[spoiler]Time is always against us[/spoiler]

i wanted this to be based on some external column like identity or Date.

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-07 : 04:44:38

quote:
Originally posted by visakh16

Are you using DISTINCT?

yeah, i had used it; now have removed it in my final query..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources